Formula Help Needed

[dcofer]

Hello All,

I hope someone can help me. I have data that is formatted as below on the left and need it formatted as displayed to the right. Some of the numbers have decimal points and some do not. That is one of the problems. The formula I am using takes the numbers that have decimal points and extracts the data on each side of the decimal point. The formula returns an error if the number does not have a decimal point, but I fizxed that with the IFERROR function. The formula I am using almost works perfectly. The only problem I am still having is if the data has cents that ends in zero, such as 123.60 or 55.70, or 95.40 (any number that is a multiple of 10), then the formula does not put the extra zero at the end.

What I am trying to accomplish is convert the number on the left (which is a payment) to the number on the right and it must include two numbers on the right to represent the cents. I hope I have explained this well enough to be understood. Any zero amount must be displayed as "000". Again, my formula works perfectly except when the number on the left has cents that are a multiple of 10.


I am using the formula below.

=IFERROR(LEFT(G5,FIND(".",G5)-1)&RIGHT(G5,LEN(G5)-FIND(".",G5)),G5&"00")

How can I make a number such as 56.10 to be displayed as 5610 instead of 561?

Examples:

100.20 needs to be displayed as 10020 -- (This on is the one that does not work.)
12.00 needs to be displayed as 1200
0.00 needs to be displayed as 000
114.25 needs to be displayed as 11425

Thanks in advance for you help. Hopefully someone can help me tweak this formula to work or provide a better one that will work.

Thanks,

David

 

[kpark91]

Can't you just multiply it by hundred and custom format the cells as "000"?

 

[dcofer]

Hello kpark91,

Thanks, that seems to work. That was so simple. I was obviously trying to overcomplicate the problem. How remarkably simple the solution was.

Thanks!

David