Find number/letter string with formula

[Obied70]

Hi, I've tried and tried but cannot seem to find an answer to do what I need. I have item descriptions in excel that contain weights...25G, 5G, etc. I need to quickly isolate these using the number/letter combo.

Example: MILK CORE 594G,KIT

I need to flag yes/no based on that 594G string in the description. Any takers? Thanks in advance

 

[kweaver]

Is there any pattern to the string? E.G., always 4 characters before the comma, always 4 characters after the 2nd space, ???

 

[Joe4]

In these cases, it is usually best to show a handful of examples, trying to cover the different formats/situations that we encounter, and then post your expected results along with that data set.
And as kweaver mentioned, be sure to mention if there is any "pattern" that they will all follow.

Otherwise the solution you get may work on the example you posted, but not on the rest of your data!

 

[Obied70]

Here are some more examples. There really is no pattern to go off of which is making it more tricky.

CNCRM 10PK 127G, WH

HCN 24/43G

CHER 5.5G, BACK, REVINK

C KIT 549G/30CT, I/OSHR

 

[kweaver]

What results do you want from the examples above?

Would you also indicate (change your profile) what version of Excel you run?!

 

[Fluff]

What version of Excel are you using & on what platform?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Obied70]

A result could be a simple "Yes" or "No". Right now I have the following formula which isnt perfect.

=IFERROR(SUBSTITUTE(MID(A2,FIND(CHAR(160),SUBSTITUTE(LEFT(A2,FIND("G",A2))," ",CHAR(160),LEN(LEFT(A2,FIND("G",A2)))-LEN(SUBSTITUTE(LEFT(A2,FIND("G",A2))," ",""))))+1,FIND("G",A2)-FIND(CHAR(160),SUBSTITUTE(LEFT(A2,FIND("G",A2))," ",CHAR(160),LEN(LEFT(A2,FIND("G",A2)))-LEN(SUBSTITUTE(LEFT(A2,FIND("G",A2))," ",""))))),"G",""),"")

This returns the characters that precede the G so in my case, if the characters preceding the G are numbers, I know I have to check those.

 

[kweaver]

Would Text to Columns help if you used ",", "/" and, " " as the delimiters?

 

[jtakw]

Hi,

Based on your samples in Post #4, as Kweaver asked, what Actual results do you need (besides Yes/No)

 

[Obied70]

Would Text to Columns help if you used ",", "/" and, " " as the delimiters?

I tried a variation of that but since the characters don't always fall in the same sequence it didn't seem to work but I'll try that combination.

Hi,

Based on your samples in Post #4, as Kweaver asked, what Actual results do you need (besides Yes/No)

All I need is a yes/no so then I can filter on yes or no and make sure other attributes are correct for the lines I'm reviewing. The end goal is to ensure that lines with 50G or 100G or 5.5G, etc. are allocated to the correct sales org