Extract specifically formatted string from a larger string

Hello all,

I have a column of text/number string cells. I want to copy out a specifically formatted string from these cells: ###x### (i.e. 300x600, 500x250, 600x250).

They can be located anywhere in the string, they can be preceded by different delimiters (-, _, [a space]). They may or may not have text following it, by either the space, _ or -.

Because it's not located in any specific part of the string, I can't use Trim Left or Right. I can't do Text to Columns because they are all defined by different delimiters and may have text following it. These numbers aren't the only numbers in the string, but they are the only ones with the ###x### formatting.

An example of what my column looks like:

Elantra 2011-300x600
1055868_AMEXOpen_Units-300x600 Boing Boing
300x250 House Ad
9253258_WorldCup2010_300x250
Sprout300x600_V2
300 X 600 Ad

DOes anyone have any suggestions? Hints?

Maybe:

=MID(A1,SEARCH("???x???",A1,1),7)

Good spot

As a vba function:

Public Function Extractx(rng As Range) As String
Dim regEx As Object
Dim regExM As Object


 Set regEx = CreateObject("vbscript.regexp")
    With regEx
        .Pattern = "[0-9][0-9][0-9][xX][0-9][0-9][0-9]"
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
    End With
     
    Set regExM = regEx.Execute(rng.Value)
    Extractx = regExM(0)
    
End Function

No problem, I assumed that wouldn't meet the requirements of the OP

Public Function Extractx(rng As Range) As String
Dim regEx As Object
Dim regExM As Object


 Set regEx = CreateObject("vbscript.regexp")
    With regEx
        .Pattern = "[0-9][0-9][0-9][xX][0-9][0-9][0-9]"
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
    End With
     
    Set regExM = regEx.Execute(Replace(rng.Value, " ", ""))
    Extractx = regExM(0)
    
End Function

No probs , a slightly more flexible solution, allowing Regex string and whether spaces should be removed

Public Function Extractx(rng As Range, regExPattern As String, NoSpaces As Boolean) As String
Dim regEx As Object
Dim regExM As Object



 Set regEx = CreateObject("vbscript.regexp")
    With regEx
        .Pattern = regExPattern
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
    End With
    
    If NoSpaces = True Then
        Set regExM = regEx.Execute(Replace(rng.Value, " ", ""))
    Else
        Set regExM = regEx.Execute(rng.Value)
    End If
    
        Extractx = regExM(0)

    
    
End Function

Hi all!

Thanks immensely for all your suggestions and coding! How would I implement this VBA coding in my excel sheet? Does it require an add-on to my excel programme?

Also, is there an alternative to the Morefunc add-on for the following code: =REGEX.MID(SUBSTITUTE(A1," ",""),"[0-9][0-9][0-9][xX][0-9][0-9][0-9]",,FALSE)

I have Excel 2011 (mac) and Morefunc is for pre-2007

That's ok! I still appreciate it! Maybe if someone could advise how I can implement that VBA code or point me in the right direction please?

@NBVC in the function itself, makes it a little more re-usable

e.g

=extractx(A1,"[0-9][0-9][0-9][xX][0-9][0-9][0-9]",TRUE)

@thb, I'm not sure that my code will work for you since it uses windows scripting and I very much doubt that mac will support it. I'll have a think for an alternative and come back to you

That worked brilliantly! And I learnt how to open VBA!

Now to trying to understand the code...

Thanks to everyone for all your advice, suggestions and codes. It was incredibly helpful!