HOW DO I EXTRACT NUMBERS FROM TEXT STRING

  • Thread starter Thread starter AndyF
  • Start date Start date
A

AndyF

I need to extract specific numbers from a text string that varies.

For example: I need to extract the second weight from each text i.e 9.
' GW/NW: 10/9 kgs'

Can anyone help please?
 
Hi,

Not really enough information but this works for your example.

=MID(A1,FIND("/",A1,FIND("/",A1,1)+1)+1,1)

Mike
 
Thanks Mike. This formula works for this example, but is there any way I can
extract any number from the text as the spacing and length of digits may
differ.

eg.

(NETT) - 68/58kgs
/NET: 338/320 KG

I only need the figure always after the '/'.

Regards,
 
I need to extract specific numbers from a text string that varies.

For example: I need to extract the second weight from each text i.e 9.
' GW/NW: 10/9 kgs'

Can anyone help please?
Click to expand...

It's pretty simple to do using a UDF utilizing Regular Expressions.

To enter the UDF,
<alt-F11> opens the VB Editor
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this, enter the formula:
=RegexSub(A1,".*/(\d+).*","$1")

The relevant pattern is

".*/(\d+).*

which separates out the digit(s) following a "/" from everything else in the
string and captures into "reference 1"

The "$1" means to substitute only what was captured in reference 1 for the
entire string.

The string needs to be all on one single line. If it is on multiple lines, we
need to change the pattern.

============================
Option Explicit

Function RegexSub(Str As String, SrchFor As String, ReplWith As String) As
String
Dim objRegExp As Object

Set objRegExp = CreateObject("vbscript.regexp")
With objRegExp
.Pattern = SrchFor
.IgnoreCase = True
.Global = True
.MultiLine = True
End With

RegexSub = objRegExp.Replace(Str, ReplWith)

End Function
==================================
--ron
 
Then this would do that
=MID(A1,FIND("/",A1,FIND("/",A1,1)+1)+1,(FIND("
kgs",A1,1)-FIND("/",A1,FIND("/",A1,1)+1)))

Watch the word wrap, mind there is a space before the kgs
 
Back
Top