VBA to extract variable length number, following a variable character

lbanham

Board Regular
Joined
Feb 17, 2011
Messages
50
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

Text String Number
lif(55)n.123 fr123
lif(5500)n. 567 (home)567
efg(2200)S.8910 May8910

<colgroup><col><col></colgroup><tbody>
</tbody>


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 :)
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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...
Code:
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
 
Upvote 0
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.

Code:
//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/
 
Last edited:
Upvote 0
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.
 
Upvote 0
Here is the Mcode assuming that there are spaces and non spaces as earlier described.

Code:
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
 
Upvote 0
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>
 
Last edited:
Upvote 0
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...
Code:
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
 
Upvote 0
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:

AB
1
2lif(55)n.123 fr123
3lif(5500)n. 567 (home)567
4efg(2200)S.8910 May8910

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:189.15px;"><col style="width:76.04px;"></colgroup><tbody>
</tbody>

CellFormula
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>
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top