PDA

View Full Version : Solved: Convert % value to text using IF

joelle
06-30-2005, 03:50 PM
Hello Experts,

Is there a way to set an IF statement to convert a % value to text.
for example:
cell B1 is for a discount percent (10.25%)
then the IF formula in C1 would be:
if B1 is > 0,
then
C1 equals to B1 but in "text" format,
otherwise, C1 is blank.

Thanks a lot,
Nee

mdmackillop
06-30-2005, 03:58 PM
Try
=IF(B1>0,TEXT(B1,"0.00%"),"")
or if you don't want a formula in the cell, this function will put the text only into the corresponding cell in column C

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column() = 2 Then
Selection.NumberFormat = "@"
Target.Offset(0, 1).Formula = Target * 100 & "%"
End If
End Sub

joelle
06-30-2005, 04:30 PM
Hello mdmackillop,

Thanks a lot for the quick reply.
Yes, I did try a "similar" formula in the past but it did not work properly
in Europe (I'm in USA).
I used this (for 14.25%):
=IF(B1>0,TEXT(B1,"##.##%"),"")
and in Europe, they saw this 142.5% !!!

Was it because I used ##.##% instead of having to use 0.00%
This is much simpler than vba if works. I'll change my formula to
=IF(B1>0,TEXT(B1,"0.00%"),"") and will ask them to test it.

I'll let you know. Thanks a lot and have a Great 4th!
(it's my sister birthday too, not mine :()

Best regards,
Nee

xld
06-30-2005, 04:46 PM
Yes, I did try a "similar" formula in the past but it did not work properly
in Europe (I'm in USA).
I used this (for 14.25%):
=IF(B1>0,TEXT(B1,"##.##%"),"")
and in Europe, they saw this 142.5% !!!

Was it because I used ##.##% instead of having to use 0.00%
This is much simpler than vba if works. I'll change my formula to
=IF(B1>0,TEXT(B1,"0.00%"),"") and will ask them to test it.

I'm in Europe, and your format gives 14.25% for me. There is no actual reason why MDs formula would work and yours not, the difference is that his would show 14% as 14.00%, yours would show as 14.%.

That is a very odd situation.

mdmackillop
07-01-2005, 12:35 AM
Hi Nee,
I have no problem with your formula either!

joelle
07-01-2005, 09:44 AM
Hello XLD and MD,

Wow - I'm impressed by the quick writeback - like you guys are just in my neighborhood (I wish!)

Thank you much XLD for looking at my formula from Europe! when I have not heard back from a soul there from my company branch in France/UK ...

Anyhow, I have no idea about why they saw it differently in the past with the point shifted one place to the right. Right or left -- they are equally bad.

Anyhow, thanks again and I will go ahead mark this thread solved. After
that I have to pack up beers for the 4th!
(who said girls dont like beers?)

Have a wonderful weekend everyone.
Nee :beerchug:

xld
07-01-2005, 10:21 AM
Thank you much XLD for looking at my formula from Europe! when I have not heard back from a soul there from my company branch in France/UK ...

Anyhow, I have no idea about why they saw it differently in the past with the point shifted one place to the right. Right or left -- they are equally bad.

There might be an issue in France, as they use . (dot) as a thousands separator, and , (Comma) as the decimal separator.

(who said girls dont like beers?)

Have a wonderful weekend everyone.

All the girls I know like beer (except my daughters :)). No point in knowing them if they didn't :yes

Enjoy the fourth.