Convert % value to text using IF

[joelle]

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.

Please help me with this IF in C1.
Thanks a lot,
Nee

[mdmackillop]

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]

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%
as in your formula??
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

[Bob Phillips]

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%
as in your formula??
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]

Hi Nee,
I have no problem with your formula either!

[joelle]

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

[Bob Phillips]

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

Enjoy the fourth.