Text extraction

[mrh15]

I have data like this in column a

Butter SO SOFT 500G P/M


How to extract the "500" value - i.e. the 3 numbers before the G?

Any help is much appreciated

 

[Domenic]

Try...

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($A$1:INDEX(A:A,LEN($A$1)))))

Hope this helps!

 

[Domenic]

Actually, if the number is always 3 digits in length, the following would suffice...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),3)+0

Hope this helps!

 

[mrh15]

That is immense!!!

One small point - it does not work for these:

Butter smaller 5 X 100G

 

[Domenic]

If the letter 'G' always follows a three digit number, try...

=MID(A1,FIND("G ",A1&" ")-3,3)

If the number is always 3 digits in length, try...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789",IF(ISNUMBER(SEARCH(" x ",A1)),SEARCH(" x ",A1),1))),3)

Otherwise, try...

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789",IF(ISNUMBER(SEARCH(" x ",A1)),SEARCH(" x ",A1),1))),ROW($A$1:INDEX(A:A,LEN($A$1)))))

Hope this helps!