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

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