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!!

 

[Andrew Poulsom]

How about?

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

 

[barry houdini]

Assuming data in A2 down try this formula in B2 copied down

=LEFT(A2,FIND("X",A2)-1)*MID(SUBSTITUTE(A2,"GM",""),FIND("X",A2)+1,9)

 

[mbk0523]

Perfect.
You have saved the day again! Thank you

 

[VoG]

Try

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

 

[njimack]

=left(a1,find("x",a1)-1)*mid(a1,find("x",a1)+1,find(" ",a1)-(find("x",a1)+1))

 

[Gary's Student]

How about:

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

 

[Andrew Poulsom]

How about:

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

There's no need for the --. The * does the necessary coercion.

 

[hurgadion]

Hi,
i propose an another Solution. Choose a Name and attribute a formula of the form:

=EVALUATE("=" & (SUBSTITUTE(SUBSTITUTE(A2," GM", ""),"X","*")))

Best regards.

 

[Gary's Student]

Thank you Andrew.