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?

Give this formula a try..

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

-- 
Rick (MVP - Excel)


"Roger on Excel" <RogeronExcel@discussions.microsoft.com> wrote in message 
news:D8AD503B-2E50-4FC9-BF4A-0534989A0972@microsoft.com...
>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? 

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)


"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:%23yP6dFJaKHA.5300@TK2MSFTNGP02.phx.gbl...
> Give this formula a try..
>
> =LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99)))
>
> -- 
> Rick (MVP - Excel)
>
>
> "Roger on Excel" <RogeronExcel@discussions.microsoft.com> wrote in message 
> news:D8AD503B-2E50-4FC9-BF4A-0534989A0972@microsoft.com...
>>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?
> 

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
-- 
If this helps, please remember to click yes.


"Roger on Excel" wrote:

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

Thanks Rick,

best regards,

Roger

"Rick Rothstein" wrote:

> 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)
> 
> 
> "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
> news:%23yP6dFJaKHA.5300@TK2MSFTNGP02.phx.gbl...
> > Give this formula a try..
> >
> > =LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99)))
> >
> > -- 
> > Rick (MVP - Excel)
> >
> >
> > "Roger on Excel" <RogeronExcel@discussions.microsoft.com> wrote in message 
> > news:D8AD503B-2E50-4FC9-BF4A-0534989A0972@microsoft.com...
> >>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?
> > 
> 
> .
> 

Thanks Paul,

Roger

"Paul C" wrote:

> 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
> -- 
> If this helps, please remember to click yes.
> 
> 
> "Roger on Excel" wrote:
> 
> > 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?