Extract specifically formatted string from a larger string

**thb** · 10-11-2011, 09:55 AM

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?

**Kyle123** · 10-11-2011, 10:24 AM

Maybe:

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

**NBVC** · 10-11-2011, 10:36 AM

since the data is not consistent enough (there could be x's with strings, like AMEX)... then perhaps you can try installing a free addin from here: Morefunc and use formula:

=REGEX.MID(SUBSTITUTE(A1," ",""),"[0-9][0-9][0-9][xX][0-9][0-9][0-9]",,FALSE)

where A1 contains first string to purge.

Else somebody may be able to supply a VBA solution using regular expressions.

**Kyle123** · 10-11-2011, 11:03 AM

Good spot

As a vba function:

Please Login or Register  to view this content.

**NBVC** · 10-11-2011, 11:10 AM

Hi Kyle and thanks for supplying a VBA alternative for the OP.

However, it returns #VALUE! error for the last sample in the original post because of spaces around the X.... I used the SUBSITITUTE() function to deal with it.. can you adjust your code accordingly for the OP (and me too

)

**Kyle123** · 10-11-2011, 11:16 AM

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

Please Login or Register  to view this content.

**NBVC** · 10-11-2011, 11:20 AM

You might be right, but now, the OP has a choice...

and I learned something too... thanks Kyle

**Kyle123** · 10-11-2011, 11:21 AM

No probs

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

Please Login or Register  to view this content.

**NBVC** · 10-11-2011, 11:28 AM

Where do you actually establish the regex pattern in that code?

**thb** · 10-11-2011, 11:29 AM

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?

**thb** · 10-11-2011, 11:31 AM

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

**NBVC** · 10-11-2011, 11:36 AM

I don't think Morefunc is compatible with Mac.. I should have noted that from your profile, sorry....

**thb** · 10-11-2011, 11:38 AM

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?

**Kyle123** · 10-11-2011, 11:39 AM

@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

**Kyle123** · 10-11-2011, 11:53 AM

Try this, it might not work in all circumstances and there's probably a more elegant way to do it, but it works for your examples:

Please Login or Register  to view this content.

@thb to use this right click on a sheet tab > view code.

In the VBE, right click in the project window - on the left where your sheets are listed, insert > module

Paste the above in the blank page it gives you.

You can then use it as a normal function ie. =ExtractMac(A1)

**thb** · 10-11-2011, 12:04 PM

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!