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.

 

[Peter_SSs]

Welcome to the MrExcel board!

Your request could be interpreted as wanting
64060|701110669156
or
6|4|0|6|0|7|0|...

Please clarify the expected results & reasoning for all 3 of your samples, especially since the last two also contain numerical values in what appears to be dates.

 

[Rick Rothstein]

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.

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>

What do you consider as "numbers" in those text string (I ask because of the dot-delimited date looking items... are they one number or three or not a number at all)? Make this easy for us here and show us the answers you want for the three examples you posted.

 

[nasirs]

Welcome to the MrExcel board!

Your request could be interpreted as wanting
64060|701110669156
or
6|4|0|6|0|7|0|...

Please clarify the expected results & reasoning for all 3 of your samples, especially since the last two also contain numerical values in what appears to be dates.

Thank you for the quick response and sorry I wasn't able to respond you timely.
In-fact I want each a every numeric sentence should extracted separately in with "|" sign like this 64060|701110669156.
However I don't need date, whether the date come out OR not, I wont mind.
Hope it is clear to you...

 

[nasirs]

What do you consider as "numbers" in those text string (I ask because of the dot-delimited date looking items... are they one number or three or not a number at all)? Make this easy for us here and show us the answers you want for the three examples you posted.

Thank you for the quick response and sorry as I wasn't able to respond you timely.
In-fact I'm considering numbers to "Numeric Values" in the text strings, However I don't need date, whether the date come out OR not, I wont mind.
Hope it is clear to you...

 

[XOR LX]

Hi,

https://excelxor.com/2014/10/27/extracting-numbers-from-a-string-3-all-numbers-to-individual-cells/

Let me know if you have any problems.

Regards

 

[Rick Rothstein]

Thank you for the quick response and sorry as I wasn't able to respond you timely.
In-fact I'm considering numbers to "Numeric Values" in the text strings, However I don't need date, whether the date come out OR not, I wont mind.

Give this UDF (user defined function a try)...

Function Nums(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9.]" Then Mid(S, X) = " "
  Next
  Nums = Replace(Replace(Application.Trim(S), " ", "|"), "|.", "")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Nums just like it was a built-in Excel function. For example,

=Nums(B2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[nasirs]

Give this UDF (user defined function a try)...

Function Nums(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9.]" Then Mid(S, X) = " "
  Next
  Nums = Replace(Replace(Application.Trim(S), " ", "|"), "|.", "")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Nums just like it was a built-in Excel function. For example,

=Nums(B2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Ohh great...
Its working perfect and my all worries are solved.
Thank you so very much...

 

[Peter_SSs]

I'm guessing it isn't going to happen with your data, but if you did have a string of say "abc123def1.3265.601ghi", the red part is not a date so my interpretation of your requirement would be an expected result of "123|1|3265|601".

The following udf would return the result above, and also does omit "dates"
However, for the moment, the function counts "dates" to be any combination of 1 or 2 digits for "days" and "months" and 2 or 4 digits for "years", it does not check that any such occurrence is a legitimate date (but it could).

I've also included an option to use a different delimiter for the results if you want.
=GetNums(B2) will use the default "|" as delimiter, but for example
=GetNums(B2,"-") would use a dash as the delimiter

Function GetNums(s As String, Optional delim As String = "|") As String
  Static RX As Object

  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = "\D+|((\d{1,2}\.){2}(\d{2}|\d{4})(?=\D|$))"
  s = RX.Replace(s, " ")
  RX.Pattern = " +"
  s = RX.Replace(s, delim)
  RX.Pattern = "(^\" & delim & ")|(\" & delim & "$)"
  GetNums = RX.Replace(s, "")
End Function

 

[nasirs]

I'm guessing it isn't going to happen with your data, but if you did have a string of say "abc123def1.3265.601ghi", the red part is not a date so my interpretation of your requirement would be an expected result of "123|1|3265|601".

The following udf would return the result above, and also does omit "dates"
However, for the moment, the function counts "dates" to be any combination of 1 or 2 digits for "days" and "months" and 2 or 4 digits for "years", it does not check that any such occurrence is a legitimate date (but it could).

I've also included an option to use a different delimiter for the results if you want.
=GetNums(B2) will use the default "|" as delimiter, but for example
=GetNums(B2,"-") would use a dash as the delimiter

Function GetNums(s As String, Optional delim As String = "|") As String
  Static RX As Object

  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = "\D+|((\d{1,2}\.){2}(\d{2}|\d{4})(?=\D|$))"
  s = RX.Replace(s, " ")
  RX.Pattern = " +"
  s = RX.Replace(s, delim)
  RX.Pattern = "(^\" & delim & ")|(\" & delim & "$)"
  GetNums = RX.Replace(s, "")
End Function

That's also a very good, rather impressive...
Serving the purpose...
Thank you so much and appreciated your efforts...

 

[Peter_SSs]

Thank you so much and appreciated your efforts...

You are welcome. At least you have choices.