VBA to extract variable length number, following a variable character

[lbanham]

HI,

I have text strings of a variable length that contain a number i want to extract. The number will follow either, F. F. (space at the end) or Sans
the number will vary in length and then will be followed by a space or another letter.

examples

[TABLE="width: 238"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Text String [/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]lif(55)n.123 fr[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD]lif(5500)n. 567 (home)[/TD]
[TD="align: right"]567[/TD]
[/TR]
[TR]
[TD]efg(2200)S.8910 May[/TD]
[TD="align: right"]8910[/TD]
[/TR]
</tbody>[/TABLE]


Can you advise of a macro to extract this? i have tried various formula combinations, arrays using a name range of the variables, but i cannot get it to consistently work.

Thank you in advance

 

[Scott Huish]

=LOOKUP(9.999999999E+307,MID(A2,FIND(".",A2)+1,ROW(INDIRECT("1:15")))+0)

Excel 2010
	A	B
1	Text String	Number
2	lif(55)n.123 fr	123
3	lif(5500)n. 567 (home)	567
4	efg(2200)S.8910 May	8910
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(9.999999999E+307,MID(A2,FIND(".",A2)+1,ROW(INDIRECT("1:15")))+0)
B3	=LOOKUP(9.999999999E+307,MID(A3,FIND(".",A3)+1,ROW(INDIRECT("1:15")))+0)
B4	=LOOKUP(9.999999999E+307,MID(A4,FIND(".",A4)+1,ROW(INDIRECT("1:15")))+0)

 

[DanteAmor]

Thanks Dante, there are occasion when a string may have an earlier dot in it. i.e. (max 62.63)N. 1134 END5, again I would only want the 1134.
appreciate the help.

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:189.15px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >lif(55)n.123 fr</td><td style="text-align:right; ">123</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >lif(5500)n. 567 (home)</td><td style="text-align:right; ">567</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >efg(2200)S.8910 May</td><td style="text-align:right; ">8910</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >(max 62.63)N. 1134 END5</td><td style="text-align:right; ">1134</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(MID(SUBSTITUTE(A2,")",REPT(" ",100)),100,100),".",REPT(" ",100)),100,100))," ",REPT(" ",100)),100))+0</td></tr></table></td></tr></table>

 

[Rick Rothstein]

...but always follows the N. or "N. " etc and then their is a space before the next alpha character.

The problem with a description like you have given is that we here have no knowledge about what your data looks like, so that "etc" is meaningless to us. Is it only specific letters followed by a dot (meaning more dots that do not follow the specific character could follow the number you want) or is the number after the last dot in the text that you want?

 

[ZVI]

Hi,
You may also try this user defined function solution:

Function GetNumber(Txt As String)
' Returns number in Txt by the template:
' not_digit & dot & (optional)space & number & space
' Usage in a cell formula: =GetNumber(A1)
  Static RegEx As Object
  If RegEx Is Nothing Then
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True
    RegEx.Pattern = "\D\. ?(\d+) "
    ' where:
    ' "\D"  = not digit
    ' "\."  = dot symbol
    ' " ?"  = optional space symbol
    ' "\d+" = one or more digits
    ' " "   = space symbol
    'NOTE: after changing the pattern hit Run-Reset button,
    '      because of Static object usage
  End If
  With RegEx.Execute(Txt)
    If .Count Then
      GetNumber = .Item(0).Submatches(0)
    Else
      GetNumber = vbNullString
    End If
  End With
End Function

If space char is not required at the end of the pattern (read comments for more details),
then use RegEx.Pattern = "\D\. ?(\d+)"
but don't forget to hit Reset button in VBE-Run menu to implement that changing.

Put text in cell A1 and use formula =GetNumber(A1)
or test it by the below code:

Sub Test()
  Debug.Print 1, GetNumber("efg(22.02)N.8910 May")
  Debug.Print 2, GetNumber("efg(22.02)S. 8910 May")
  Debug.Print 3, GetNumber("efg(22.02)N.8910May")
  Debug.Print 4, GetNumber("efg(22.02)S. 8910May")
End Sub

 

[lbanham]

The problem with a description like you have given is that we here have no knowledge about what your data looks like, so that "etc" is meaningless to us. Is it only specific letters followed by a dot (meaning more dots that do not follow the specific character could follow the number you want) or is the number after the last dot in the text that you want?

apologies for not being clear. The four variables are below: N,N.,N. ,OMGI_STRALC

[TABLE="width: 607"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]String[/TD]
[TD]Number expected[/TD]
[TD]Preceding text[/TD]
[/TR]
[TR]
[TD](MOV.61-62)N. 5618 HD OMAG.FEB.18[/TD]
[TD]5618[/TD]
[TD]N.[/TD]
[/TR]
[TR]
[TD]N.52 TORRES WI FI (CAD/cad.) gen-18[/TD]
[TD]52[/TD]
[TD]N. (+space)[/TD]
[/TR]
[TR]
[TD]OMGI_STRALC 8 CHAIR[/TD]
[TD]8[/TD]
[TD]OMGI_STRALC (+Space)[/TD]
[/TR]
[TR]
[TD](MOV.61-62)N5618 HD OMAG.FEB.18[/TD]
[TD]5618[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]

 

[lbanham]

Hi Alan, i get an error saying query did not complete. i created a table named 14 for the data. any ideas? Thanks

 

[ZVI]

The four variables are below: N,N.,N. ,OMGI_STRALC

[TABLE="width: 607"]
<tbody>[TR]
[TD]String[/TD]
[TD]Number expected[/TD]
[TD]Preceding text[/TD]
[/TR]
[TR]
[TD](MOV.61-62)N. 5618 HD OMAG.FEB.18[/TD]
[TD]5618[/TD]
[TD]N.[/TD]
[/TR]
[TR]
[TD]N.52 TORRES WI FI (CAD/cad.) gen-18[/TD]
[TD]52[/TD]
[TD]N. (+space)[/TD]
[/TR]
[TR]
[TD]OMGI_STRALC 8 CHAIR[/TD]
[TD]8[/TD]
[TD]OMGI_STRALC (+Space)[/TD]
[/TR]
[TR]
[TD](MOV.61-62)N5618 HD OMAG.FEB.18[/TD]
[TD]5618[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]

Try this user defined function:

Function GetNumber(Txt As String)
' Returns number in Txt by the template:
' ("N" or "OMGI_STRALC") & optional_dot & optional_space & number & space
' Usage in a cell formula: =GetNumber(A1)
  Static RegEx As Object
  If RegEx Is Nothing Then
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True
    RegEx.Pattern = "(N|OMGI_STRALC)\.? ?(\d+) "
    ' where:
    ' "(N|OMGI_STRALC)"  = "N" or "OMGI_STRALC"
    ' "\.?"  = optional dot symbol
    ' " ?"  = optional space symbol
    ' "\d+" = one or more digits
    ' " "   = space symbol
    'NOTE: after changing the pattern hit Run-Reset button,
    '      because of Static object usage
  End If
  With RegEx.Execute(Txt)
    If .Count Then
      GetNumber = .Item(0).Submatches(1)
    Else
      GetNumber = vbNullString
    End If
  End With
End Function

 

[alansidman]

Did you make this equal to Table14. You can change the Table name in the query if you use something else
[TABLE="class: cms_table, width: 238"]
<tbody>[TR]
[TD]Text String[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]lif(55)n.123 fr[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD]lif(5500)n. 567 (home)[/TD]
[TD="align: right"]567[/TD]
[/TR]
[TR]
[TD]efg(2200)S.8910 May[/TD]
[TD="align: right"]8910[/TD]
[/TR]
</tbody>[/TABLE]