Excel: Custom Number formatting in excel 2007


This is a report out of SAP, the column with Pounds 1 is automatically coming out of the system, the column Pounds 2 is a calculation that I had to manually do for over 5,000 line items. In column "Pounds 1" we have "LB" and "G" at the end of each number. I am trying to create a IF statement for everything with a "LB" to return the same number, but if it ends with a "G" multiply it by .0022046 to convert Grams (G) into Pounds (LB) (cell# *.0022046). I think the reason it is not working, bc it has some type of custom number formatting. When I do a Ctrl 1 on the cell it takes me to "Custom format cell" and when I do the comma style (,) it displays only the number and drops the ("LB" and "G"), any help would be greatly appreciated. Thank you.

Pounds 1 Pounds 2
253,219.372 LB 253,219
941,352.152 LB 941,352
937,189.400 LB 937,189
6,060.960 LB 6,061
3,333.528 LB 3,334
370,732.032 LB 370,732
12,920.544 LB 12,921
195,819.624 LB 195,820
48,087.000 LB 48,087
3,861.000 LB 3,861
92,664.000 LB 92,664
28,957.500 LB 28,958
975,744 G 2,151
900,900 G 1,986
5,082,000 G 11,204
2,753,520 G 6,070
698,280 G 1,539


This question generated 26 answers. To proceed to the answers, click here.

This thread is current as of October 15, 2014.


For more resources for Microsoft Excel