Is there a function or format to remove 'oz' from '12oz' i..

Thanks all for the different suggestions. These will keep me busy for a
while.

"Ron Rosenfeld" wrote:

> On Wed, 20 Aug 2008 06:36:10 -0700, Skip <Skip.TakeThisOut@discussions.microsoft.com>
> wrote:
>
> >Thanks for your reply. I apologize for not being clearer. Absolutely my
> >fault. I am familiar with the edit/replace and left/mid/right functions, but
> >the spreadsheet I'm expecting back will have multiple rows of varying user
> >inputs. For instance, I could have over 400 rows, each with a different
> >value, such as 12oz, 120g, 1500mls, etc. There will not be any constant
> >length in either the number or text values, hence my question. Worse case,
> >I'll use the find/replace to strip off the alpha characters, but was hoping
> >Excel had a way of returning only the numeric values in this situation.
> >
> >Skip
>
>
> =LOOKUP(9.9E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
> A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))
>
> will return the numbers. You could then edit/copy edit/paste special/values
> to eliminate the formula.
> --ron
>

There is a function called Edit>Replace

Note: after you have removed "oz" and replaced with nothingyou will have to
format the cells as General.

Then...........Copy an empty cell.

Select the range of "numbers" and edit>paste special>add>ok>esc.


Gord Dibben MS Excel MVP

On Wed, 20 Aug 2008 06:03:01 -0700, Skip <Skip.DeleteThis@discussions.microsoft.com>
wrote:

>I'm trying to see if there is a way to take another user's input of "12oz",
>say in cell A1, and convert it to the numeric value of just '12' in another
>cell, say B1, so I can use the numeric value for calculations?