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

 

[Rick Rothstein]

It looks to me like you are trying to retrieve the last number contained in a text string. If that is correct, then give this function a try...

Function LastNumber(S As String) As Long
  Dim X As Long, Z As Long
  For X = Len(S) To 1 Step -1
    If Mid(S, X, 1) Like "#" Then
      For Z = X To 1 Step -1
        If Mid(S, Z, 1) Like "[!0-9]" Then
          LastNumber = Val(Mid(S, Z + 1))
          Exit Function
        End If
      Next
    End If
  Next
End Function

 

[alansidman]

Using Power Query, I Split the data on spaces and then removed the uneeded columns. Here is the Mcode. Check below for how to employ this.

//Your source data is called Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitColumnbySpaceDelimiter = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    RemovedUneededColumns = Table.RemoveColumns(SplitColumnbySpaceDelimiter,{"Column1.1", "Column1.3"})
in
    RemovedUneededColumns

https://excel.solutions/2017/11/power-query-paste-code-video/

 

[Rick Rothstein]

Using Power Query, I Split the data on spaces and then removed the uneeded columns.

What about the OP's first example where there is no space between the "n." and the number "123"?

 

[alansidman]

Rick,
Assumed it was a typo as he indicated "space at end"

If not the case then I could split on the period and then again on the space after the number.

Ibanham--please advise cell structure includes spaces or not.

 

[alansidman]

Here is the Mcode assuming that there are spaces and non spaces as earlier described.

let
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    SplitColumnbyPeriodDelimiter = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    TrimmedText = Table.TransformColumns(SplitColumnbyPeriodDelimiter,{{"Column1.2", Text.Trim, type text}}),
    SplitColumnbySpaceDelimiter = Table.SplitColumn(TrimmedText, "Column1.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
    RemovedUneededColumns = Table.SelectColumns(SplitColumnbySpaceDelimiter,{"Column1.2.1"})
in
    RemovedUneededColumns

 

[DanteAmor]

If you always have:

characters dot number characters
lif(5500)n.567(home)

then it can be like 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></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 >=VALUE(TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A2,"."," "))," ",REPT(" ",100)),100,99)))</td></tr><tr><td >B3</td><td >=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A3,"."," "))," ",REPT(" ",100)),100,99))+0</td></tr><tr><td >B4</td><td >=--TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A4,"."," "))," ",REPT(" ",100)),100,99))</td></tr></table></td></tr></table>

 

[lbanham]

Thanks Rick for the quick reply, it is not always the last number in the string, but always follows the N. or "N. " etc and then their is a space before the next alpha character.
I am logged out of my main laptop now but will try this in my file in the morning (I'm in Scotland)

It looks to me like you are trying to retrieve the last number contained in a text string. If that is correct, then give this function a try...

Function LastNumber(S As String) As Long
  Dim X As Long, Z As Long
  For X = Len(S) To 1 Step -1
    If Mid(S, X, 1) Like "#" Then
      For Z = X To 1 Step -1
        If Mid(S, Z, 1) Like "[!0-9]" Then
          LastNumber = Val(Mid(S, Z + 1))
          Exit Function
        End If
      Next
    End If
  Next
End Function

 

[lbanham]

some have a space and some do not. N.1234 or N. 456.
thanks

 

[lbanham]

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.

If you always have:

characters dot number characters
lif(5500)n.567(home)

then it can be like this:

	A	B
lif(55)n.123 fr
lif(5500)n. 567 (home)
efg(2200)S.8910 May

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:189.15px;"><col style="width:76.04px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]123[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]567[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]8910[/TD]

</tbody>

Cell Formula B2 =VALUE(TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A2,"."," "))," ",REPT(" ",100)),100,99))) B3 =TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A3,"."," "))," ",REPT(" ",100)),100,99))+0 B4 =--TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A4,"."," "))," ",REPT(" ",100)),100,99)) <tbody> </tbody>

<tbody>
</tbody>