[VBA] Can't find anything to help, need to extract a string around a character, between two other sets of characters.

[RockandGrohl]

Hi guys, have a string that looks like this:

Standard_16x4_3UK
or
BAC_T_9x6_1UK
or
Filler_17.5x9_1UK

What I need to do is find x, then extract text around it, between the first underscores it finds. Output would thus be:

16x4, 9x6 and 17.5x9


So far I have code to find the position value of a certain character using InStr, but this doesn't help me get the characters to the left and right of the 'x' up until it finds the underscore.

Right now I'm torn between using a combination of Instr, Find, Mid & Len, or using a split function... And I don't know where to start on either.

Thanks.

 

[Norie]

Are the values around the 'x' always numeric?

 

[RockandGrohl]

Are the values around the 'x' always numeric?

That is correct, yes But there can be a decimal place in them so that's why I just want to extract the full string between the underscores

 

[RockandGrohl]

Are the values around the 'x' always numeric?

What if I used split on delimiter underscore, then searched each split string for "x" and then just used that? I wonder if that's possible.

 

[Norie]

Try this UDF that use a regular expression.

Function GetDimensions(strDescription As String) As String
Dim RegEx As Object
Dim Matches As Object
Dim strPattern As String

    Set RegEx = CreateObject("VBScript.RegExp")
    
    strPattern = "\d*\.?\d*x\d*\.?\d*"
    
    RegEx.Pattern = strPattern
    
    Set Matches = RegEx.Execute(strDescription)
    
    If Matches.Count = 1 Then
        GetDimensions = Matches.Item(0)
    End If
    
End Function

 

[pgc01]

Hi Norie

Didn't work for me if there are more "x"'s in the text, like

Fixed_2x4_4UK (although the OP does not consider this case)

 

[pgc01]

What I need to do is find x, then extract text around it, between the first underscores it finds.

If that's really what you need, also:

Function GetDimensions1(strDescription As String) As String
    GetDimensions1 = Filter(Split(strDescription, "_"), "x")(0)
End Function

 

[Norie]

If the dimensions are always surrounded by underscored the function can be adjusted like this.

Function GetDimensions(strDescription As String) As String
Dim RegEx As Object
Dim Matches As Object
Dim strPattern As String

    Set RegEx = CreateObject("VBScript.RegExp")
    
    strPattern = "_\d*\.?\d*x\d*\.?\d*_"
    
    RegEx.Pattern = strPattern
    
    Set Matches = RegEx.Execute(strDescription)
    
    If Matches.Count = 1 Then
        GetDimensions = Mid(Matches.Item(0), 2, Len(Matches.Item(0)) - 2)
    End If
    
End Function

 

[RockandGrohl]

If that's really what you need, also:

Function GetDimensions1(strDescription As String) As String
    GetDimensions1 = Filter(Split(strDescription, "_"), "x")(0)
End Function

Thank you Norie & Thank you PGC01, I think I will use PGC's as I'm sure the "Filter" command for VBA will come in handy at another point in time and it's good to have a working example to see how to implement it in the future. That's what I was thinking in my OP, about finding the split array with the x in it and using it.

Good work both of you and much appreciated for your time. Every day is a school day.

 

[pgc01]

I'm glad it helped. Thanks for the feedback.