Left-Right-Mid-Find-Len question

[mbk0523]

Hi,

I know I should be using left/right/mid/find/len, but I am not good at writing those formulas. Hoping someone can help quickly.

Data in one columns looks like this:

1X10 GM
1X150 GM
1X8.3 GM
10X24 GM
2X6.2 GM
etc...

I need to get the total number of grams from here. So I need the output to look like this:

10
150
8.3
240
12.4

Basically, if the number before the X is greater than 1, multiply that by the number after the X (and before the GM). If the number before the X equals 1, just take the number after the X (and before the GM).

Thanks!!

 

[Marcol]

I might be tempted to use SEARCH() rather than FIND() to allow for 1x8.3 GM

=LEFT(A1,SEARCH("X",A1)-1)*MID(A1,SEARCH("X",A1)+1,SEARCH(" ",A1)-SEARCH("X",A1))