I just faced an annoying problem in Excel. I need to convert an euro sum (e..g. €49.80) into text formatted as the integer part four characters long with leading zeros and accordingly cents two characters long with leading zeros. Example, euro 49.80 should be presented as 004980. When I calculate the cent part it will result in 79, not 80. I have to find out a formula which always presents the cent figure correctly, not too small not too big.

Thanx Hakan

On Tue, 22 Jan 2013 07:50:13 -0800 (PST), Håkan Björkström wrote:

I just faced an annoying problem in Excel. I need to convert an euro sum (e.g. €49.80) into text formatted as the integer part four characters long with leading zeros and accordingly cents two characters long with leading zeros. Example, euro 49.80 should be presented as 004980. When I calculate the cent part it will result in 79, not 80. I have to find out a formula which always presents the cent figure correctly, not too small not too big.

Thanx Hakan

Without understanding what you mean by this: "When I calculate the cent part it will result in 79, not 80", I would suggest:

A1: Your euro value

=TEXT(A1*100,"000000")

If you do not obtain the answer you require, you will need to explain what you mean by the above sentence, and provide examples of the data and formulas you are actually using.

Hi Hakan,

Am Tue, 22 Jan 2013 07:50:13 -0800 (PST) schrieb Håkan Björkström:

I just faced an annoying problem in Excel. I need to convert an euro sum (e.g. ?49.80) into text formatted as the integer part four characters long with leading zeros and accordingly cents two characters long with leading zeros. Example, euro 49.80 should be presented as 004980. When I calculate the cent part it will result in 79, not 80. I have to find out a formula which always presents the cent figure correctly, not too small not too big.

your euro sum in A1:
=ROUND(A1*100,-1) and custom format "000000"
or as text:
=TEXT(ROUND(A1*100,-1),"000000")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Tuesday, 22 January 2013 17:58:31 UTC+2, Claus Busch wrote:
Hi Hakan,



Am Tue, 22 Jan 2013 07:50:13 -0800 (PST) schrieb H�kan Bj�rkstr�m:



I just faced an annoying problem in Excel. I need to convert an euro sum (e.g. ?49.80) into text formatted as the integer part four characters long with leading zeros and accordingly cents two characters long with leading zeros. Example, euro 49.80 should be presented as 004980. When I calculate the cent part it will result in 79, not 80. I have to find out a formula which always presents the cent figure correctly, not too small not too big.



your euro sum in A1:

=ROUND(A1*100,-1) and custom format "000000"

or as text:

=TEXT(ROUND(A1*100,-1),"000000")





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Easier than I expected. I tried to separately handle integer and decimals and the formula for cents was Right("00"&100*(c3-Int(c3));2), which gave me 79, when C3 contained value 49.80.
Thanx a lot
Hakan

Hi Hakan,

Am Tue, 22 Jan 2013 08:15:43 -0800 (PST) schrieb Håkan Björkström:

Easier than I expected. I tried to separately handle integer and decimals and the formula for cents was Right("00"&100*(c3-Int(c3));2), which gave me 79, when C3 contained value 49.80.

there ist an error into the formula. Please change to:
=TEXT(ROUND(A1*100,0),"000000")
or
=ROUND(A1*100,0)
with custom number format


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2