Text funtion, but display as %?

**trillium** · 02-26-2012, 09:47 PM

Hi I am trying to create a statement in excel that will combine text and references to numbers, however the numbers need to be displayed as a %.

I am trying to use the Text function : TEXT(value, format_text) but instead of the options showing, I want to show it as % .... not possible?

My % is in cell D33 (and is a reference from somewhere else) and is formatted as %.

My text in cell A1 to show will look like this:

=IF(Language="ENG","ABC level is "&D33&" of annual premium","Provisionné ABC "&D33&" des primes annuelles")

However, the current result is :

ABC level is 0.1 of annual premium

I want it to look like this:

ABC level is 10% of annual premium

any tricks?

**abousetta** · 02-26-2012, 10:17 PM

This should do the trick:

Please Login or Register  to view this content.

**nmhung49** · 02-26-2012, 10:23 PM

Try
=IF(Language="ENG","ABC level is "&D33&" of annual premium","Provisionné ABC "& TEXT(D33,"#%")&" des primes annuelles")

**vlady** · 02-26-2012, 11:38 PM

@ nmhung49

how about 0% ?

I'll go with abousetta ..

=TEXT((D33* 100),"0")&"%"

displays 0% if zero..

**nmhung49** · 02-26-2012, 11:46 PM

@vlady
How about 0%, it is easy, I have not tested the case of cells value = 0
Try
=IF(Language="ENG","ABC level is "&D33&" of annual premium","Provisionné ABC "& TEXT(D33,"#0%")&" des primes annuelles")

**vlady** · 02-26-2012, 11:58 PM

nice ..

now i like your's.

**trillium** · 02-27-2012, 10:46 AM

Thanks everyone for your suggestions!!

Abousetta, worked like a charm! I had tried to multiply but was doing it in function part, not the first part like you did, and didn't think to just add the % at the end like that! Beautiful! cheers!