Custom Number formatting in excel 2007

S

slamar1211

New Member
Joined
Oct 15, 2014
Messages
17
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

<colgroup><col style="width: 94pt; mso-width-source: userset; mso-width-alt: 4571;" span="2" width="125"> <col style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;" width="98"> <tbody>
</tbody>
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
slamar1211 said:
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.
Click to expand...

Can you copy the custom number formatting that you see so we can see the logic behind assigned G and LB? We can use the same logic in the IF formula.
 
Upvote 0
When I hit Ctrl 1, it takes me to format cells / Custom.
#,##0 "G" (This is for the G)

#,##0.000 "LB" (This is for the LB)

Let me know if this is what you needed?
 
Upvote 0
slamar1211 said:
When I hit Ctrl 1, it takes me to format cells / Custom.
#,##0 "G" (This is for the G)

#,##0.000 "LB" (This is for the LB)

Let me know if this is what you needed?
Click to expand...

Well....it's not quite what I expected...but try this.

The logic is basically...if there's a period (denoting decimal places) then it's a LB, otherwise it's a G. It might not work. If you look into the cell that contains 698,280 G, what does it show up in the formula bar?

Code: 
=IFERROR(IF(FIND(".",A1)>0,A1,0),A1*0.0022046)
 
Upvote 0
It looks like it worked, but I am wondering why 3 of them did not work?


Pounds 1 Pounds 2 Diff
253,219.372 LB 253,219.37 253,219 -
941,352.152 LB 941,352.15 941,352 -
937,189.400 LB 937,189.40 937,189 -
6,060.960 LB 6,060.96 6,061 -
3,333.528 LB 3,333.53 3,334 -
370,732.032 LB 370,732.03 370,732 -
12,920.544 LB 12,920.54 12,921 -
195,819.624 LB 195,819.62 195,820 -
48,087.000 LB 106.01 48,087 (47,981)
3,861.000 LB 8.51 3,861 (3,852)
92,664.000 LB 204.29 92,664 (92,460)
28,957.500 LB 28,957.50 28,958 -
975,744 G 2,151.13 2,151 -
900,900 G 1,986.12 1,986 -
5,082,000 G 11,203.78 11,204 -
2,753,520 G 6,070.41 6,070 -
698,280 G 1,539.43 1,539 -

<colgroup><col style="width: 94pt; mso-width-source: userset; mso-width-alt: 4571;" span="2" width="125"> <col style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;" span="2" width="98"> <tbody>
</tbody>
 
Upvote 0
So I was afraid that was going to happen. The answer is that those three don't contain a decimal place because they have ending 0's and, thus, no ACTUAL decimal places in the raw data. Hmm...I think we're getting into UDF or macro territory. Who assigns the custom formats?
 
Upvote 0
The System is automatically doing it when I extract the file. Not sure who in our IT dept handles that. I was getting so excited, bc it was working....lol
 
Upvote 0
Well if you're cool with a macro...I love macros...so....LETS DO THIS!

Code: 
Sub convertToLBS()

Application.ScreenUpdating = False
    For x = 2 To Cells(Rows.Count, "A").End(xlUp).Row 'CHANGE "A" TO COLUMN
        If Cells(x, 1).NumberFormat = "#,##0 ""G""" Then
            Cells(x, 2) = Cells(x, 1) * 0.0022046
        Else
            Cells(x, 2) = Cells(x, 1)
        End If
    Next x
Application.ScreenUpdating = True
End Sub
 
Upvote 0
You could use this formula

=CONVERT(LEFT(A1,FIND(" ",A1&" ")), IF(RIGHT(A1,1)="g","g","lbm"), "lbm") & "LB"
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,980
Messages
6,122,563
Members
449,088
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top