Converting range in pounds to kilograms

[FMash]

Hi,
I am trying to convert pounds to kilograms in a column. I know how to do that for a single number =CONVERT(1,"lbm","kg"). My question is there a way to do a range of number? From 90-300 pounds.
The sheet I am creating is for medical purposes and individuals weight varies.


Thank you
FMash

 

[Scott T]

You can change the format in text to what you want I used on decimal place

Excel 2010
	A	B	C
1	90-300 pounds		40.8-136.1 kg
Sheet1

Cell Formulas
Range	Formula
C1	=TEXT(CONVERT(LEFT(A1,SEARCH("-",A1)-1),"lbm","kg"),"0.0")&"-"&TEXT(CONVERT(MID(SUBSTITUTE(A1," ",REPT(" ",99)),SEARCH("-",SUBSTITUTE(A1," ",REPT(" ",99)))+1,99)+0,"lbm","kg"),"0.0")&" kg"

 

[FMash]

So that converts the range yes. However, I am looking for something that can set up a column. Each cell within that column would automatically change the number entered(in pounds) to kilograms. So if I entered 150 (lbs) it would change to 68.03 automatically. Is there a way to set up a column to automatically multiply by 0.45? 1lb=0.4535

 

[Scott T]

To change the value in the cell that it was entered into you would need VBA.

Right click on the tab you want the code to run on and select view code and past the code below into the VBA editor.
change Target.Column = 1 to the column you want A=1, B=2....
I assume row one has headers so no number entered in row 1 is changed
you will need to save the file as a macro enabled file type like .XLSM

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column = 1 And Target.Row <> 1 And IsNumeric(Target)) Then
Application.EnableEvents = False
Target = Round(Target * 0.4535, 2)
End If
Application.EnableEvents = True
End Sub