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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
810
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Are the values around the 'x' always numeric?
 
Upvote 0
Try this UDF that use a regular expression.
VBA Code:
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
 
Upvote 0
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)
 
Upvote 0
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:

VBA Code:
Function GetDimensions1(strDescription As String) As String
    GetDimensions1 = Filter(Split(strDescription, "_"), "x")(0)
End Function
 
Upvote 0
If the dimensions are always surrounded by underscored the function can be adjusted like this.
VBA Code:
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
 
Upvote 0
If that's really what you need, also:

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

Forum statistics

Threads
1,224,828
Messages
6,181,213
Members
453,024
Latest member
Wingit77

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