VBA code to extract groups of numbers from strings

[surya80]

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.

 

[jmacleary]

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
	A	B	C	D
1		Part	Section	Desired result
2	abcdef1234ghij5678klmn	1234	5678	Part-1234, Section-5678
3	fred3456and2345end			Part-3456, Section-2345
Sheet1

Cell Formulas
Range	Formula
B2	B2	=getpart(A2)
C2	C2	=getsection(A2)
D2	D2	="Part-"&B2&", Section-"&C2
D3	D3	="Part-"&getpart(A3)&", Section-"&getsection(A3)

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]

you can try a version of this , replace A7 with the cell you want.

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

 

[surya80]

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
	A	B	C	D
1		Part	Section	Desired result
2	abcdef1234ghij5678klmn	1234	5678	Part-1234, Section-5678
3	fred3456and2345end			Part-3456, Section-2345
Sheet1

Cell Formulas
Range	Formula
B2	B2	=getpart(A2)
C2	C2	=getsection(A2)
D2	D2	="Part-"&B2&", Section-"&C2
D3	D3	="Part-"&getpart(A3)&", Section-"&getsection(A3)

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]

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]

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]

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
	A	B	C	D
6	fred3456and2345end875fshsrt			875
Sheet1

Cell Formulas
Range	Formula
D6	D6	=getnumeric(A6,3)

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]

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
	A	B	C	D
6	fred3456and2345end875fshsrt			875
Sheet1

Cell Formulas
Range	Formula
D6	D6	=getnumeric(A6,3)

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]

Here's another option

Book1
	A	B	C	D	E
1	fred3456and2345end875fshsrt			875
2
Sheet1

Cell Formulas
Range	Formula
D1	D1	=GetNumber(A1, 3)

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

 

[jmacleary]

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.