How would you extract the number the precedes a given word in a cell?

The structure of my data (in each cell) is: Alpha Jan 13 35.00 Grams. So it is a record of an item (alpha in this cell), date (Jan 13), and number of grams. I need to find every cell that has this kind of record in a large data set, and after every occurrence of "Grams" I want to extract the number of grams. In this case 35.00. Note the place numbers are not equal. For example in another cell the record could be "Beta March 20 350.00 Grams".

Is the Text to Columns feature an option? It's on the Data tab.

this will extract the 35 (or 350) and convert it to a value...

=VALUE(MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1)+1,SEARCH(".",A1,1)-(SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1)+1)))

happy to help, and maybe consider point 3 below

Good people, I have been trying to retrace this thread for a long time, but I thougt I had conducted it in email. I am glad to relocate it.

Now on the same question, my data does vary in length and decimal places, and I need to read the number precisely as included in the dataset. How would you modify the code

=VALUE(MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1)+1,SEARCH(".",A1,1)-(SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)+1)+1)))

so that you read the number precisely, including the decimals, noting that some could be just one decimal places, others could be as long as 4 decimals? As the code is currently it returns 2 zeros for decimal places. Which entry of the code dictates how many places the code reads after the period? I am having difficulties catching up to the logic of programing in exel and the programs I am familiar with are currently unreachable - so I have to learn.

I will appreciate your help.

wow, interesting seeing some old solutions here, thanks for that

IF your real data format is consistent with your sample data...Name space month space day space amount space Grams (alpha month date amount grams) then here is a different approach
=--SUBSTITUTE(MID(A2,FIND("xx",SUBSTITUTE(A2," ","xx",3),1)+1,99)," Grams","")

Most appreciated FDibbins,

But I made an error in making it seem like "grams" is universal in all fields. That's not the case. Your original formulary works really well, except for the decimal places, if you can find a way of updating that.


Actually in the original problem 'grams' was universal, but now I have many other codes in the units column.

Regards.

OK try this...
=--LEFT(MID(A2,FIND("xx",SUBSTITUTE(A2," ","xx",3),1)+1,99),FIND(" ",MID(A2,FIND("xx",SUBSTITUTE(A2," ","xx",3),1)+1,99),1))

Sure.

THis is the main part, it finds where the values start...
MID(A2,FIND("xx",SUBSTITUTE(A2," ","xx",3),1)+1,99)

I use SUBSTITUTE() to swap the 3rd space with xx, so it can be found
SUBSTITUTE(A2," ","xx",3)
FIND("xx",SUBSTITUTE(A2," ","xx",3),1)

THen I use MID() to return a bunch of values from that position onwards. It doesnt really matter how many, at this point, I am just interested in everything from the value onwards
MID(A2,FIND("xx",SUBSTITUTE(A2," ","xx",3),1)+1,99)
the +1 after the FIND() is for the space that was found

We now have something like this...
35 grams

All that remains is to find that space, and take everything to the left of it. So essentially, we have this..
=--LEFT("350 grams",FIND(" ",MID(A2,FIND("xx",SUBSTITUTE(A2," ","xx",3),1)+1,99),1))

Finally, because LEFT/MID/RIGHT return text, I used -- to convert that to a value

Hope that helps?