I'm looking for a formula to extract a variable length substring from a string. The character format of the source string varies as well as the format of the substring to be extracted.
I want to extract the numbers preceding the "G" or "g" character towards the end, but it is not always at the end of string.
For example, the source strings may look like this:
PRG AP H&S 12/40G
PRG US RF ORG 6/8PK/18G SNCK STK STLK
PRG CN SCO 8/8CT/19G SNCKSTK
PRG BRZ Chdr Cheese 12/40g
PRG AP CHDR CHZ 18X165G
PRG ANZ BBQ 14/150G
PRG ANZ HONEY MUSTD 14/150G DOWNUNDER
PRG LA Cheese 40g
PRG LA BBQ - Gilroy Seasoning - 137g
PRG AP CHDR CHZ 18X165G
PRG US LD BKD PT 14 181G SPRST OTS
PRG US RC MX LYR DIP 14/1 81G SPR STK
and I want to extract the following substrings:
40G
18G
19G
40g
165G
150G
150G
40g
137g
165G
181G
1 81G
I tried the following formula but it doesn't always work for all of the source string formats listed above...
=IFERROR((SUBSTITUTE(MID(A1,FIND("/",A1)+1,FIND("G",A1,FIND("/",A1))-(FIND("/",A1)))," ","")),SUBSTITUTE(MID(A1,FIND("X",A1)+1,FIND("G",A1,FIND("X",A1))-(FIND("X",A1)))," ",""))
If there's a " " space before the "181G" like this "PRG US CHDR CHZ 14 181G SPR STK OTS" it returns a #VALUE! error. The above formula also doesn't work if there are multiple "/" characters in the source string.
Any help would be appreciated! I'd rather the formula be as short and efficient as possible, but I'm not afraid of long formulas if it's necessary.
Bookmarks