Extracting numbers from a string containg letters at the end

[Roger on Excel]

I have a column of cells which contain material quantities in units of grams
(g) or millilitres (ml). I need to extract the numbers from the strings and
paste them into the adjacent cell. It would also be nice to place the unit
in the next cell afterwards also

For example:

3.04g
25ml
24.2 ml
5 g
3g

Notice that there sometimes is a space after the number which would need to
be removed.

Can anyone help?

 

[Rick Rothstein]

Give this formula a try..

=LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99)))

 

[Rick Rothstein]

Oops, I missed the second part of your question. Assuming your first value
is in A1 and you put the formula I gave you in B1, then put this in C1...

=TRIM(SUBSTITUTE(A1,C1,""))

By the way, my earlier posted formula and this one can be copied down as
needed.

 

[Paul C]

assuming data in a1
B1
=VALUE(LEFT(A1,IF(ISERROR(FIND("g",A1)),FIND("ml",A1),FIND("g",A1))-1))
would give you the number value and
C1
=if(iserror(find("g",a1)),"ml","g")
would give you the unit

 

[Roger on Excel]

Thanks Rick,

best regards,

Roger

Oops, I missed the second part of your question. Assuming your first value
is in A1 and you put the formula I gave you in B1, then put this in C1...

=TRIM(SUBSTITUTE(A1,C1,""))

By the way, my earlier posted formula and this one can be copied down as
needed.

--
Rick (MVP - Excel)




.

 

[Roger on Excel]

Thanks Paul,

Roger

assuming data in a1
B1
=VALUE(LEFT(A1,IF(ISERROR(FIND("g",A1)),FIND("ml",A1),FIND("g",A1))-1))
would give you the number value and
C1
=if(iserror(find("g",a1)),"ml","g")
would give you the unit