Converting lb AND oz from one cell to grams in another

[HVHCEXCEL]

Hi everybody,

I'm new to this forum and excel for that matter and have been going crazy trying to figure something out that's probably very simple. I have a column with a bunch of birthweights in the following form "7 lb 1.1 oz" and would like to convert it in another cell into grams (i.e. 7 lb 1.1 oz --> 3205 grams). Any help would be greatly appreciated.

Thanks!

 

[Chrisdontm]

If you had: 7 in A1
& 1.1 in A2
& Total them in A3

↓

=CONVERT(A1,"lbm","g")
=CONVERT(A2,"ozm","g")
=SUM(A1:A2)

<colgroup><col style="mso-width-source:userset;mso-width-alt:4209;width:89pt" width="118"> </colgroup><tbody>
[TD="width: 118, align: right"]3175.14659
[/TD]

[TD="align: right"]

<colgroup><col width="118"></colgroup><tbody>
[TD="width: 118, align: right"] 31.18447544
[/TD]

</tbody>

[/TD]

[TD="align: right"]3206.331065
[/TD]

</tbody>

 

[HVHCEXCEL]

Thanks for your reply. Do you know if there is a way to do this if the lb and oz is in the same cell (i.e. "7 lb 1.1 oz" in A1)?

 

[alansidman]

You would need to split the cell into multiple columns to make it work. As shown in your example you have a string (text) field and will need to have two numerical fields to make it happen. You can split the cell using the Excel built in function Text to columns delimited on space.

 

[ukmikeb]

Welcome to the MrExcel Forum.

Excel Workbook
	A	B
2	7 lb 1.1 oz	3206.33017
3	7 lb 1.1 oz	3206.33017
HVHEXCEL

Excel 2007

Cell Formulas
Range	Formula
B2	=CONVERT(LEFT(A2,FIND("lb",A2)-1)*16+MID(A2,FIND("lb",A2)+2,FIND("oz",A2)-1-FIND("lb",A2)-2)+0,"ozm","g")
B3	=CONVERT(TRIM(LEFT(SUBSTITUTE(A3," ",REPT(" ",4)),4))*16+TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",4)),10,8)),"ozm","g")

hth

 

[HVHCEXCEL]

Thanks, that is one heck of a formula but it works like a charm! Glad there's people like you to help out with this!

 

[ukmikeb]

Thanks for the feedback.

Pleased to have helped solve your problem.

Good luck with your project.