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?

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

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

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

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

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

"Mike H" wrote:

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

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

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

On Wed, 20 Aug 2008 06:36:10 -0700, Skip
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

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

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

"Mike H" wrote:

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

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

There is a function called EditReplace

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 editpaste specialaddokesc.


Gord Dibben MS Excel MVP

On Wed, 20 Aug 2008 06:03:01 -0700, Skip
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?