Extract variable length substring from a string

**charron9** · 05-20-2013, 04:28 PM

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.

**Teethless mama** · 05-20-2013, 05:44 PM

Try this:

=IFERROR(LOOKUP(1E99,LEFT(MID(A1,LOOKUP(1E99,SEARCH(ROW($1:$9999)&"G",A1)),9),ROW($1:$9))+0)&"G","")

**charron9** · 05-22-2013, 11:18 AM

@Teethless mama:

THANK YOU so much! This works perfectly

I had been struggling with this for a few days trying to think of how to include all the exceptions.

**charron9** · 05-23-2013, 10:21 AM

One thing I just noticed...when the source string has a space in the middle of the substring I'm trying to extract, it only pulls the part after the space....

PRG US RC MX LYR DIP 14/1 81G SPR STK

formula extracts: 81G
should be: 1 81G or 181G

I can use the =SUBSTITUTE formula to change "1 81G" to "181G"

Any suggestions? Thanks

**charron9** · 05-23-2013, 10:22 AM

Re: Extract variable length substring from a string

One thing I just noticed...when the source string has a space in the middle of the substring I'm trying to extract, it only pulls the part after the space....

PRG US RC MX LYR DIP 14/1 81G SPR STK

formula extracts: 81G
should be: 1 81G or 181G

I can use the =SUBSTITUTE formula to change "1 81G" to "181G"

Any suggestions? Thanks

**Ken64** · 05-24-2013, 11:52 AM

Good morning,
I used the above suggestion on the following string...
Padstack: 65R45D Type: through Inner pads: Optional
and changed the search letter from a "G" to "R" and removed the second G as I only require the number... =IFERROR(LOOKUP(1E+99,LEFT(MID(A33,LOOKUP(1E+99,SEARCH(ROW($1:$9999)&"R",A33)),9),ROW($1:$9))+0),"") It worked perfectly and resulted in the number 65. However, sometimes the "R" will be "S". Since there will always be a space before the 65 I change the "R" in the formula to a " " and changed the word LEFT to Right...=IFERROR(LOOKUP(1E+99,RIGHT(MID(A33,LOOKUP(1E+99,SEARCH(ROW($1:$9999)&" ",A33)),9),ROW($1:$9))+0),""), but it did not work. What am I doing wrong?

Thanks for any help.
Ken