Extract Only Numbers From Text String

[mgirvin]

Dear Smartest Excelers Around,

Is there a one cell formula that could take this string in cell A1:

45t*&65/

and extract only the numbers and deliver this

4565

to a single cell?

The formula would have to be able to deal with all 255 ASCII characters and be copied down a column.

 

[DocAElstein]

Edit:

.....
In the very simplest terms, it produces an Array, “ vertical “ 1 Dimension, of consecutive indices....
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25}

_ It is sort of defining here how long the String is that you are wanting to work on / how many “things are compared pair wise” as it were.
........

I sort of mean a 2 Dimension , 1 "column" Array... but for the "pair wise analysis thing" inside a formula the orientation is lost a bit ( I think.. ), so 1 D is also OK to say here..

 

[thanks_in_advance]

Hi,

This has nearly solved me many hours!! Is there a way to return the numbers found in a cell separated by a comma? i.e. x24y32ksdf752 becomes 24,32,752

I have been using this so far:
=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN($A$2))),1))*
ROW(INDIRECT("1:"&LEN($A$2))),0),ROW(INDIRECT("1:"&LEN($A$2))))+1,1)*10^ROW(INDIRECT("1:"&LEN($A$2)))/10)

 

[thanks_in_advance]

Even better would be to extract the numbers separately into cells to the right of the original cell.

 

[RoryA]

If you want to do it with pure formulas, have a read of this: https://excelxor.com/2014/10/27/extracting-numbers-from-a-string-3-all-numbers-to-individual-cells/

If a VBA function is an option, let me know.

 

[thanks_in_advance]

worked like a charm, thanks

 

[DBABaracus]

I've been playing around with a different approach (it's still a work in progress, so no final formulas yet).

I have a huge spreadsheet of data that includes phone numbers, many of which are poorly formatted. Normal ones appear as "(555) 555-5555" but then there are entries like "555-555-5555 C.", "Cell - 555-555-5555", "Home 555 555 5555", and so forth.

My goal is to get it down to just a string of 10 numbers, which can then be auto-formatted by Excel. Having the non-numeric characters appear inconsistently at the beginning, middle, or end of the strings is a problem though. So what I've been playing around with is using a combination of CLEAN(), SUBSTITUTE(), and CHAR() to replace non-numeric characters with non-printable ones, which are then removed via CLEAN().

Not sure if I'll reach a final answer on it, and it may take more than 1 column of calculated cells to pull it off. But it's been a fun puzzle on a slow day.

 

[István Hirsch]

I think it is simpler to Substitute non-numeric characters directly with a blank character than Substitute them with a non-printable character first, then Clean the string.

 

[Rick Rothstein]

Return the all the digits for a string of text containing digits and non-digits mixed together

Posted previously by Lars-Åke Aspelin (on a different forum I think)...

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 14 digits in the input string.
(Following digits will be shown as zeroes.)

Maybe of no practical use, but it will also handle the following two cases correctly:

- a "0" as the first digit in the input will be shown correctly in the output

- an input without any digits at all will give the empty string as output (rather than 0).

 

[Peter_SSs]

I've been playing around with a different approach (it's still a work in progress, so no final formulas yet).

I have a huge spreadsheet of data that includes phone numbers, many of which are poorly formatted. Normal ones appear as "(555) 555-5555" but then there are entries like "555-555-5555 C.", "Cell - 555-555-5555", "Home 555 555 5555", and so forth.

My goal is to get it down to just a string of 10 numbers, which can then be auto-formatted by Excel. Having the non-numeric characters appear inconsistently at the beginning, middle, or end of the strings is a problem though. So what I've been playing around with is using a combination of CLEAN(), SUBSTITUTE(), and CHAR() to replace non-numeric characters with non-printable ones, which are then removed via CLEAN().

Not sure if I'll reach a final answer on it, and it may take more than 1 column of calculated cells to pull it off. But it's been a fun puzzle on a slow day.

Perhaps you could consider a user-defined function?
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Digits(s As String) As String
  Dim i As Long
  
  For i = 1 To Len(s)
    If Mid(s, i, 1) Like "#" Then Digits = Digits & Mid(s, i, 1)
  Next i
End Function

Excel Workbook
	A	B
1	(555) 555-5555	5555555555
2	555-555-5555 C.	5555555555
3	Cell - 555-555-5555	5555555555
4	Home 555 555 5555	5555555555
Sheet1

 

[nasirs]

Perhaps you could consider a user-defined function?
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Digits(s As String) As String
  Dim i As Long
  
  For i = 1 To Len(s)
    If Mid(s, i, 1) Like "#" Then Digits = Digits & Mid(s, i, 1)
  Next i
End Function

Sheet1

*	A	B
1	(555) 555-5555	5555555555
2	555-555-5555 C.	5555555555
3	Cell - 555-555-5555	5555555555
4	Home 555 555 5555	5555555555

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =Digits(A1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hi there, though I'm new here, hence pardon me if anything went wrong.

In fact I have strings like given below, from which I wanted to extract the numbers only and each number should be separated with "|" sign.

Will appreciate if anyone can help me to solve my problem...

B2:	RR64060 CN701110669156 SANA SAFINAZ (AMIR & CO.)
B3:	COD-SALE, RR# 194067, CN# 219110904818, HOUSE OF CHRIZMA, RRDT: 01.06.2016 HBL.
B4:	COD-SALE, RR# 194076, CN# 201117425753, SINDHI DRESS, RRDT: 02.06.2016 HBL.

<tbody>
</tbody>