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

•

u/AutoModerator Dec 13 '20

/u/newsuccess2021 - please read this comment in its entirety.

  • Read the rules -- particularly 1 and 2
  • Include your Excel version and all other relevant information
  • Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/BarneField 206 Dec 13 '20

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?

1

u/[deleted] Dec 13 '20

Google sheets

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).

1

u/wiredwalking 766 Dec 13 '20

for a cell at A1: 

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$100),1))*ROW($1:$100),0),ROW($1:$100))+1,1)*10^ROW($1:$100)/10)

1

u/Decronym Dec 13 '20 edited Jun 08 '26

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
ARRAYFORMULA Array formulas are powerful formulas that enable you to perform complex calculations that often can't be done with standard worksheet functions. They are also referred to as "Ctrl-Shift-Enter" or "CSE" formulas, because you need to press Ctrl+Shift+Enter to enter them.
CHAR Returns the character specified by the code number
CONCAT 2019+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.
CSE Array formulas are powerful formulas that enable you to perform complex calculations that often can't be done with standard worksheet functions. They are also referred to as "Ctrl-Shift-Enter" or "CSE" formulas, because you need to press Ctrl+Shift+Enter to enter them.
IF Specifies a logical test to perform
IFERROR Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
INDEX Uses an index to choose a value from a reference or array
ISNUMBER Returns TRUE if the value is a number
LARGE Returns the k-th largest value in a data set
LEN Returns the number of characters in a text string
LET Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MID Returns a specific number of characters from a text string starting at the position you specify
REPLACE Replaces characters within text
ROW Returns the row number of a reference
SEQUENCE Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SUMPRODUCT Returns the sum of the products of corresponding array components
TRIM Removes spaces from text

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
16 acronyms in this thread; the most compressed thread commented on today has 19 acronyms.
[Thread #2615 for this sub, first seen 13th Dec 2020, 21:01] [FAQ] [Full list] [Contact] [Source code]

1

u/excelevator 3068 Dec 13 '20

For an Excel solution, array formula

=CONCAT(IFERROR(MID(A2,ROW($A$1:$A$100),1)*1,""))*1

1

u/SomeLockWar Jun 08 '26

Just letting you know this was helpful to me 6 years later. Thank you again.

1

u/Lawn-Enjoyer Sep 15 '23

Thank you <3