VBA code to extract groups of numbers from strings

surya80

New Member
Joined
Feb 12, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hello I've been trying to clean some data in cells and format the strings in a specific way e.g.

Input:
abcdef1234ghij5678klmn

Required format of string:
Part-1234, Section-5678

The sequence of letters preceding, in between and following the numeric digits can be of variable length and may include alphabets, spaces, hyphens, commas, periods and slashes ( "/" or "\" )

I'm just quite a novice in VBA programming I just don't know enough of it to get my head around this problem.
Thank you for any help and guidance towards solving this.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hello and welcome. I have written 2 functions which you can use to pick out the numbers. Copy the code and put it in a Module of your workbook. Then use the functions either directly (see cell D3 formula, or in bits (see formulas in B2 to D2).
Book2
ABCD
1PartSectionDesired result
2abcdef1234ghij5678klmn12345678Part-1234, Section-5678
3fred3456and2345endPart-3456, Section-2345
Sheet1
Cell Formulas
RangeFormula
B2B2=getpart(A2)
C2C2=getsection(A2)
D2D2="Part-"&B2&", Section-"&C2
D3D3="Part-"&getpart(A3)&", Section-"&getsection(A3)

VBA Code:
Function GetPart(CellRef As String)
    Dim StringLength As Integer
    Dim Started As Boolean
    Application.Volatile
    Started = False
    StringLength = Len(CellRef)
    For i = 1 To StringLength
        If IsNumeric(Mid(CellRef, i, 1)) Then
            Started = True

            Result = Result & Mid(CellRef, i, 1)
        Else
            If Started = True Then i = StringLength
        End If
    Next i
    GetPart = Result
End Function

Function GetSection(CellRef As String)
    Dim StringLength As Integer
    Dim Started As Boolean
    Application.Volatile
    Started = False
    StringLength = Len(CellRef)
    For i = StringLength To 1 Step -1
        If IsNumeric(Mid(CellRef, i, 1)) Then
            Started = True

            Result = Mid(CellRef, i, 1) & Result
        Else
            If Started = True Then i = 1
        End If
    Next i
    GetSection = Result
End Function
 

deanl33069

Board Regular
Joined
May 2, 2019
Messages
115
you can try a version of this , replace A7 with the cell you want.

=IFERROR(MID(A7,FIND(abcde,A7)+5,4)/10,"")
 

surya80

New Member
Joined
Feb 12, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hello and welcome. I have written 2 functions which you can use to pick out the numbers. Copy the code and put it in a Module of your workbook. Then use the functions either directly (see cell D3 formula, or in bits (see formulas in B2 to D2).
Book2
ABCD
1PartSectionDesired result
2abcdef1234ghij5678klmn12345678Part-1234, Section-5678
3fred3456and2345endPart-3456, Section-2345
Sheet1
Cell Formulas
RangeFormula
B2B2=getpart(A2)
C2C2=getsection(A2)
D2D2="Part-"&B2&", Section-"&C2
D3D3="Part-"&getpart(A3)&", Section-"&getsection(A3)

VBA Code:
Function GetPart(CellRef As String)
    Dim StringLength As Integer
    Dim Started As Boolean
    Application.Volatile
    Started = False
    StringLength = Len(CellRef)
    For i = 1 To StringLength
        If IsNumeric(Mid(CellRef, i, 1)) Then
            Started = True

            Result = Result & Mid(CellRef, i, 1)
        Else
            If Started = True Then i = StringLength
        End If
    Next i
    GetPart = Result
End Function

Function GetSection(CellRef As String)
    Dim StringLength As Integer
    Dim Started As Boolean
    Application.Volatile
    Started = False
    StringLength = Len(CellRef)
    For i = StringLength To 1 Step -1
        If IsNumeric(Mid(CellRef, i, 1)) Then
            Started = True

            Result = Mid(CellRef, i, 1) & Result
        Else
            If Started = True Then i = 1
        End If
    Next i
    GetSection = Result
End Function

Thank you very much sir, the code worked perfectly :) I'm quite amazed by how you broke up the problem logically into two smaller parts and tackled them. I have "Option Explicit" turned on by default so I only had to declare the counter variable "i" globally and the "Result" variable as a Variant and it was all smooth sailing after that :)
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

You're welcome and thanks for the feedback. As I was finishing them I realised I could have written a more general-purposebut I won't do that unless its needed.
 

surya80

New Member
Joined
Feb 12, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
You're welcome and thanks for the feedback. As I was finishing them I realised I could have written a more general-purposebut I won't do that unless its needed.
Just curious, what if there were three or more groups of numerals instead of just two groups, how do you think one could go about solving that problem. Would it be of any help to store the value of the numerals in a horizontal array?
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

that was what I was thinking about. This general-purpose code would do it - pass cell reference and the number of the group you want: (It could be more elegant and shorter but I don't think that matters much in this case)
Book2
ABCD
6fred3456and2345end875fshsrt875
Sheet1
Cell Formulas
RangeFormula
D6D6=getnumeric(A6,3)
VBA Code:
Function GetNumeric(CellRef As String, Position As Variant)
' enter position as an integer 1=first group, 2=second group etc.direction as 0 for first number, 1 for last number
    Dim StringLength As Integer
    Dim PosCount As Variant
    Dim i As Integer
    Dim Result As Variant
    Dim Started As Boolean
    Application.Volatile
    StringLength = Len(CellRef)
    PosCount = 1
    Started = False
   
    For i = 1 To StringLength
        If IsNumeric(Mid(CellRef, i, 1)) Then
            Started = True
            If PosCount = Position Then
                Result = Result & Mid(CellRef, i, 1)
            End If
        Else
            If Started = True Then
                PosCount = PosCount + 1
                Started = False
            End If
        End If
    Next i
    GetNumeric = Result
End Function
 

surya80

New Member
Joined
Feb 12, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
that was what I was thinking about. This general-purpose code would do it - pass cell reference and the number of the group you want: (It could be more elegant and shorter but I don't think that matters much in this case)
Book2
ABCD
6fred3456and2345end875fshsrt875
Sheet1
Cell Formulas
RangeFormula
D6D6=getnumeric(A6,3)
VBA Code:
Function GetNumeric(CellRef As String, Position As Variant)
' enter position as an integer 1=first group, 2=second group etc.direction as 0 for first number, 1 for last number
    Dim StringLength As Integer
    Dim PosCount As Variant
    Dim i As Integer
    Dim Result As Variant
    Dim Started As Boolean
    Application.Volatile
    StringLength = Len(CellRef)
    PosCount = 1
    Started = False
  
    For i = 1 To StringLength
        If IsNumeric(Mid(CellRef, i, 1)) Then
            Started = True
            If PosCount = Position Then
                Result = Result & Mid(CellRef, i, 1)
            End If
        Else
            If Started = True Then
                PosCount = PosCount + 1
                Started = False
            End If
        End If
    Next i
    GetNumeric = Result
End Function

Thank you very much, this code worked fine and pretty much fulfills what I was looking for :)
 

juddaaaa

Board Regular
Joined
Jan 4, 2020
Messages
208
Office Version
  1. 365
Platform
  1. Windows
Here's another option

Book1
ABCDE
1fred3456and2345end875fshsrt875
2
Sheet1
Cell Formulas
RangeFormula
D1D1=GetNumber(A1, 3)


VBA Code:
Function GetNumber(Inp As String, Pos As Integer) As Variant
    Dim matches As Object
    
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d+"
        .Global = True
        .MultiLine = True
        Set matches = .Execute(Inp)
    End With
    
    GetNumber = matches(Pos - 1)
End Function
 
Last edited:

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Nice one Juddaaaa. One small point - my (long-winded) solution returns a 0 if too large a group number is chosen, yours throws a #value! error.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,122
Messages
5,628,824
Members
416,342
Latest member
BlueDevil12

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
Top