Excel 2016 TEXT function ignores formatting

[dzhogov]

Hello, everyone,

I have a pivot table displaying the total sales of three brands in two columns (one as currency, the other as % of total). The currency column's numbers are formatted as currency, with thousands separator, two decimal places and "лв." after each number. For each of the three brands' row, I have created a dynamic 'label' which I have copied onto a pie chart so I have custom data values. One label reads as such:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">=A4&CHAR(10)&TEXT(GETPIVOTDATA("Sales";$A$3;"Brand";A4);"#'### лв."&CHAR(10)&TEXT(GETPIVOTDATA("Sales %";$A$3;"Brand";A4);"0.00%"))</code>The issue I am having is that the text function seems to ignore the "#'###.## лв." formatting completely. Instead of displaying "1'234'567.89 лв.", the function displays "123'456'789 лв.", misplacing the thousands separator, ignoring the decimal comma, and making the number greater that it is. The percentages are formatted correctly. Any help with this issue would be very appreciated!

 

[RoryA]

What does:

=GETPIVOTDATA("Sales";$A$3;"Brand";A4)

return?

 

[dzhogov]

What does:

=GETPIVOTDATA("Sales";$A$3;"Brand";A4)

return?

This returns the name of Brand A. I've typed it this way so that it's not "hard keyed."

Column A contains the BRAND, Column B contains SALES, and Column C contains the sales of each brand as a % of the grand total.

Example:

A | B | C
4| Brand A | 100'000.00 лв. | 50%
5| Brand B | 100'000.00 лв. | 50%

The label needs to return:

Brand A
100'000.00 лв.
50%

It currently returns:

Brand A
10'000'000.0 лв.
50%

 

[RoryA]

This returns the name of Brand A.

No, it doesn't. A GETPIVOTDATA formula won't return a name. Please tell us what that actual formula returns for you as a value, so that we can see what value the TEXT function is working with. (note there is no decimal section in your format so it will never show a decimal portion).

 

[dzhogov]

Sorry about the confusion Rory.

=GETPIVOTDATA("Sales";$A$3;"Brand";A4)

returns 100000.000

In regards to the decimal section missing in the format, I played around with it and have copy pasted a formula without the decimal section. Below I will list the formats I have tried and the value the function displays.

###,## лв. - 100000,00 лв.
###.## лв. - 10000000.00 лв.
###,### лв. - 10000,000 лв.
###.### лв. - 10000000.0 лв.
#'###,### лв. - 10'000',000 лв.
#'###.### лв. - 10'000'000.0 лв.

 

[RoryA]

What do your computer's regional settings use as thousands and decimal separators?

 

[dzhogov]

I double checked that the thousands and decimal separators within my control panel settings use ' and . and I also made sure that within Excel's options the software is matching to Windows's display language.

 

[RoryA]

That's very odd then. If you type 100000 in a cell, then apply a custom number format of

#'###.### лв

what do you see displayed in the cell?

Also, what does this formula return:

=TEXT(GETPIVOTDATA("Sales";$A$3;"Brand";A4);"#'###.### ""лв.""")

 

[dzhogov]

That's very odd then. If you type 100000 in a cell, then apply a custom number format of

#'###.### лв

what do you see displayed in the cell?

Also, what does this formula return:

=TEXT(GETPIVOTDATA("Sales";$A$3;"Brand";A4);"#'###.### ""лв.""")

Very odd indeed. This number formar returns 100'000. лв.

The strange thing is that the formula by itself returns the correct value, but it is the same old (wrong) thing if it is within the label.

 

[dzhogov]

Ah, I just noticed where the issue is.

I have moved the bracket which was at the end of the formula

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; line-height: inherit; color: rgb(51, 51, 51); font-variant: inherit; white-space: inherit; background-color: rgb(250, 250, 250); border: 0px; font-stretch: inherit; vertical-align: baseline; box-sizing: inherit;">=A4&CHAR(10)&TEXT(GETPIVOTDATA("Sales";$A$3;"Brand";A4);"#'### лв."&CHAR(10)&TEXT(GETPIVOTDATA("Sales %";$A$3;"Brand";A4);"0.00%"))</code>

When the bracket precedes the & sign after the number format (like so), the formatting is displayed correctly.

<code style="font-style: inherit; font-variant: inherit; font-weight: inherit; white-space: inherit; margin: 0px; padding: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; line-height: inherit; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250); border: 0px; font-stretch: inherit; vertical-align: baseline; box-sizing: inherit;">=A4&CHAR(10)&TEXT(GETPIVOTDATA("Sales";$A$3;"Brand";A4);"#'### лв.")&CHAR(10)&TEXT(GETPIVOTDATA("Sales %";$A$3;"Brand";A4);"0.00%")</code>

Rory, your replies were of great help, although I should have probably not overlooked this in the first place.

Thanks!