Extract numbers from text string

[bertie3856]

Hi, I have a line of text that contains numbers. I only want to extract the first set of numbers. Example, the text in cell A1 will always say "Deposit Black PB 500.00". There will always be 2 spaces after PB, however the amount will vary, from say 1.00 to 50000.00. I only want to extract the 500. Any ideas? Apologies if this has been posted previously. Thanks.

 

[LFKim2018]

Hi, I have a line of text that contains numbers. I only want to extract the first set of numbers. Example, the text in cell A1 will always say "Deposit Black PB 500.00". There will always be 2 spaces after PB, however the amount will vary, from say 1.00 to 50000.00. I only want to extract the 500. Any ideas? Apologies if this has been posted previously. Thanks.

make a copy of your workbook and test the function there - do not test it directly on your work.

place the function code below in a module, then in the cell place the formula as =Digits(A2) (example)
if you wish to convert it to numbers =value(Digits(A2)) or =Digits(A2)*1

Function Digits(ByVal S As String) As String 'Courtesy of Rick Rothstein
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9,.]" Then Mid(S, X) = Chr(1)
  Next
  Digits = Trim(Replace(S, Chr(1), ""))
End Function

 

[bertie3856]

Sorry, my first question was a bit misleading, as I forgot the other numbers. The line actually reads "Deposit Black PB 500.00 8596512555". The 500.00 or 1000.00 or whatever it is is the only variable. Your code didn't take into account the decimal point, so it returned 50000 instead of 500 or 500.00. Either would do.

 

[Rick Rothstein]

Sorry, my first question was a bit misleading, as I forgot the other numbers. The line actually reads "Deposit Black PB 500.00 8596512555". The 500.00 or 1000.00 or whatever it is is the only variable. Your code didn't take into account the decimal point, so it returned 50000 instead of 500 or 500.00. Either would do.

Give this function a try...

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

 

[bertie3856]

Give this function a try...

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

Perfect, thank you very much.

 

[jtakw]

Hi,

A formula solution, based on your samples:

Book1
	A	B
1	Deposit Black PB 500.00	500.00
2	Deposit Black PB 500.00 8596512555	500.00
3	Deposit Black PB 50000.00 8596512555	50000.00
Sheet185

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH("PB ",A1)+4,255)," ",REPT(" ",30)),30))+0

Formula copied down.
Result converted to Real Number.
Format result cell as Number to show desired decimal places.