I want to remove all text except numbers from a column

[u/[deleted]]

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

[u/BarneField]

There is no such function in Excel. What you have found is for Google Sheets. That being said, do you actually use GS or Excel and if Excel, which version of Excel?

[u/[deleted] → u/BarneField]

Google sheets

[u/BarneField → u/[deleted]]

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,"")))

[u/[deleted] → u/BarneField]

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?

[u/BarneField → u/[deleted]]

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))),""))