Is there a function or format to remove 'oz' from '12oz' in Excel?

[Skip]

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?

 

[Jarek Kujawa]

=LEFT(A1,LEN(A1)-2)+0

 

[Mike H]

Hi,

If you don't want the 'oz' in the cell simply do an

Edit|Replace
enter oz
leave the replace with box blank

Or

=LEFT(A1,SEARCH("oz",A1)-1)+0

in another cell.

Mike

 

[Jarek Kujawa]

or
=LEFT(A1,FIND("oz)-1)+0

 

[Skip]

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

 

[Jarek Kujawa]

you might try to use the following macro (select yr data first, should
be stored in a column):

Sub cus()
For Each cell In Selection
cell.Offset(0, 1) = Val(cell)
Next cell
End Sub


HIH

 

[Ron Rosenfeld]

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

 

[Mike H]

Maybe this,

Sub sonic()
For Each C In ActiveSheet.UsedRange
If Not C.HasFormula And Not IsNull(C) And Not IsEmpty(C) Then
C.Value = Val(C)
End If
Next
End Sub

Mike

 

[Skip]

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

 

[Gord Dibben]

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