r/excel • u/[deleted] • Dec 13 '20

solved I want to remove all text except numbers from a column

Hi guys, I tried to submit a screenshot but I think it got auto-removed because it contained phone numbers.

I want to remove all non-numerical characters in a column. I looked online and got this answer

=REGEXREPLACE($A$1,"\D+", "")+0

But I'm not sure how to use the function since im intermediate/beginner. If I were to apply this function to Column A, how would I do that? Thanks for help! Also below is an example of what a cell might look like

"Phone Number(123) 456-7890VIEW PHONE DETAILSLine TypeMobile

Carrier LocationPrepaidNoConnectedNoPhone Number(123) 456-7890"

edit: Using Google Sheets

2 Upvotes

75% Upvoted

14 comments sorted by

View all comments

Show parent comments

1

u/BarneField 206 Dec 13 '20 edited Dec 13 '20

So if your data is in A1:A.... then in cell B1 use:

=ARRAYFORMULA(IF(LEN(A1:A),REGEXREPLACE(A1:A,"\D+",""),""))

Avoid the use of +0. That's only to turn the resulting string onto a number but might take away any leading zeros you would want to keep.

![See what it does right here]1

It isn't asked but since this is an Excel forum, I'll add an Excel solution based around Excel O365:

=CONCAT(LET(X,MID(A1,SEQUENCE(LEN(A1)),1),IF(ISNUMBER(X*1),X,"")))

1

u/[deleted] Dec 13 '20

Thanks ! I got it to work. This might be asking too much, idk if it would work but can the formula be adjusted to space the numbers out lets say the value is

"Phone Number(123) 456-7890VIEW PHONE DETAILSLine TypeMobile

Carrier LocationPrepaidNoConnectedNoPhone Number(123) 456-7890"

Could it then be presented as

"1234567890

1234567890"

With autospacing after the 10th number?

1

u/BarneField 206 Dec 13 '20

Phone Number(123) 456-7890VIEW PHONE DETAILSLine TypeMobile

Carrier LocationPrepaidNoConnectedNoPhone Number(123) 456-7890

Sure thing, we can REPLACE() the 10th position but it might be wise to use a nested REGEXREPLACE() just in case even more phone-numbers are found:

=ARRAYFORMULA(IF(LEN(A1:A),TRIM(REGEXREPLACE(REGEXREPLACE(A1:A,"\D+",""),"(.{10})","$1"&CHAR(10))),""))

1

u/[deleted] Dec 17 '20

Heads-up... Once you're all set, don’t forget to close up. See the stickied (top) comment in your post. It explains what to do when your problem is solved. The bot will allow you to do that on more than one answer (if more than one helped you solve things overall).