Text extraction

M

mrh15

New Member
Joined
Oct 20, 2003
Messages
30
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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!
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
VBA code to extract a table
Replies
5
Views
358
Anthony47
A
M
  • Solved
Unique() filter to exclude blanks and multiple strings
Replies
2
Views
868
metcala
M
jbillh
  • Question Question
How to add up quantities that are associated ONLY with an "Identifier word" like a SKU or word across 2 or 3 sheets in the same workbook?
2
Replies
15
Views
367
Peter_SSs
Peter_SSs
T
  • Solved
Extracting and Formatting Numbers from Data Rows
2
Replies
12
Views
418
TheWes
T
C
  • Question Question
XLookup -or- Countif with leading numbers then Character
Replies
1
Views
113
Alex Blakenburg
A

Forum statistics

Threads
1,225,477
Messages
6,185,221
Members
453,283
Latest member
Shortm88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top