Custom Number Formatting with &

[lukman4068]

How do you apply custom number formatting to an & formula - i.e. ="Total left to collect is "&A2

I want the content of A2 which is a % to show as a %- any way of doing this as part of the formula?

 

[JamesW]

Hi,

You cannot add custom formatting to part of a cell that contains a formula.

You can use VBA though, like this:

Sub test()
    Cells(1, 1).Value = "Total left to collect is " & FormatPercent(Range("A2").Value)
End Sub

 

[Gerald Higgins]

At a very basic level, how about

"Total left to collect is "&a2&" %"

This will work, if your number is already something like 50, for 50%.

If your number is 0.5, for 50%, try this

"Total left to collect is "&(a2*100)&" %"

 

[JamesW]

Or you can do it that way ^^

 

[Dryver14]

you could try this

="total number to add is "&(A2*100)&" %"

this is all text though but 22% would show as 22%

 

[lukman4068]

the only issue is, the % is a formula off something else and ends up coming up as 0.28009833708730833 or whatever it is dependant on the result - which is what I want to avoid? :/

 

[Gerald Higgins]

Then use ROUND(A2*100,0) instead of A2*100.

This will give you 28% in your example.

Or if you want to show it as, say, 28.01%, use
ROUND(A2*100,2) and so on.

 

[jasonb75]

Not an ideal method, but what you asked originally can be done.

B2 uses custom format

"total left to collect is "#.##%

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 84px"><COL style="WIDTH: 184px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">0.280098337</TD><TD style="TEXT-ALIGN: right">total left to collect is 28.01%</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=A2</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1


edit:

or I misread the question, and a simple ="Total left to collect is "&TEXT(A2,"#.##%") would suffice.