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

 

[jtakw]

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

Ok, see if this works for you:

Book3.xlsx
	A	B
1	CNCRM 10PK 127G, WH	Yes
2	HCN 24/43G	Yes
3	CHER 5.5K, BACK, REVINK	No
4	C KIT 549CT/30CT, I/OSHR	No
5	CHER 5.5G, BACK, REVINK	Yes
6	C KIT 549G/30CT, I/OSHR	Yes
7	C KIT 549CT/30CT, I/OSHR, CNCRM 10PK 127G, WH	Yes
Sheet866

Cell Formulas
Range	Formula
B1:B7	B1	=IF(SUM(N(ISNUMBER(FIND({0,1,2,3,4}+{0;5}&"G",A1)))),"Yes","No")

 

[Obied70]

Ok, see if this works for you:

Book3.xlsx
	A	B
1	CNCRM 10PK 127G, WH	Yes
2	HCN 24/43G	Yes
3	CHER 5.5K, BACK, REVINK	No
4	C KIT 549CT/30CT, I/OSHR	No
5	CHER 5.5G, BACK, REVINK	Yes
6	C KIT 549G/30CT, I/OSHR	Yes
7	C KIT 549CT/30CT, I/OSHR, CNCRM 10PK 127G, WH	Yes
Sheet866

Cell Formulas
Range	Formula
B1:B7	B1	=IF(SUM(N(ISNUMBER(FIND({0,1,2,3,4}+{0;5}&"G",A1)))),"Yes","No")

That's pretty stinkin close! Thank you! Can you help me understand what exactly that formula is doing to return the yes/no?

 

[jtakw]

That's pretty stinkin close! Thank you! Can you help me understand what exactly that formula is doing to return the yes/no?

My formula in Post #11 will detect Any number from 0 thru 9 immediately followed by G, if it finds Any (0G, 1G, 2G, 3G, 4G, 5G, 6G, 7G, 8G, 9G), results YES, otherwise NO.

The formula, as it is, is Case-sensitive, the G must be Capitalized, if you need it Non-case sensitive (0G or 0g, 1G or 1g, etc.), change the FIND to SEARCH, like below:

=IF(SUM(N(ISNUMBER(SEARCH({0,1,2,3,4}+{0;5}&"G",A1)))),"Yes","No")