How to convert two numbers in one cell to another form in a separate cell
Posted by Paul Long on May 18, 2001 8:35 AM
I am trying to convert two numbers in one cell from english units to metric. The two numbers were entered and separated using the alt+enter command so that one appears over the other. I am looking for a command that will convert the two original numbers as required, and display them in the same form in another cell.
Thank you in advance for any help
Posted by cpod on May 18, 2001 9:41 AM
=TEXT(CONVERT(VALUE(LEFT(A1,SEARCH(CHAR(10),A1)-1)),"lbm","kg"),"00.0")&CHAR(10)&TEXT(CONVERT(VALUE(MID(A1,SEARCH(CHAR(10),A1)+1,100)),"lbm","kg"),"00.0")
This converts pounds to kilograms and formats the value with one decimal point.
The Convert function is part of the Data Analysis Tool Pak. You have to have that installed in order for this to work.
You have to format the cell for text wrap.
Posted by Paul Long on May 18, 2001 10:41 AM
I believe I was a bit unclear with this post. I have several columns of numbers which represent dimensions in english units. Two numbers were entered into 1 cell by entering the first number, hitting alt+enter, and entering the second number. The results of this are the first number appearing over the second in one cell, no word wrap required. In the cell next to this one, I wish to show the dimensions in mm. The calculation is to take the numbers in the 1st cell and multiply them by 25.4. The "=A1*25.4" formula does not work. The formula listed above may do a similar calculation, but I do not have the proper tool pack available to me.
Posted by Barrie Davidson on May 18, 2001 12:34 PM
Hi Paul, try this (assumes that your data is in cell A1):
=MID(A1,1,FIND(CHAR(10),A1)-1)*25.4
Barrie
Posted by Mark W. on May 18, 2001 4:03 PM
Posted by Mark W. on May 18, 2001 4:06 PM
See...
Posted by Barrie Davidson on May 18, 2001 9:08 PM
Use Mark W's formula, I misread your requirements
Posted by Paul Long on May 22, 2001 5:06 AM
Thank you all, this was very helpful. =LEFT(A1,FIND(CHAR(10),A1)-1)*25.4&CHAR(10)&RIGHT(A1,LEN(A1)-FIND(CHAR(10),A1))*25.4 ...and then apply Wrap text formatting to cells
