Excel 2016 TEXT function ignores formatting

dzhogov

New Member
Joined
Apr 1, 2019
Messages
11
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!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,495
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What does:

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

return?
 

dzhogov

New Member
Joined
Apr 1, 2019
Messages
11
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,495
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Apr 1, 2019
Messages
11

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,495
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What do your computer's regional settings use as thousands and decimal separators?
 

dzhogov

New Member
Joined
Apr 1, 2019
Messages
11

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,495
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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);"#'###.### ""лв.""")
 
Last edited:

dzhogov

New Member
Joined
Apr 1, 2019
Messages
11
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

New Member
Joined
Apr 1, 2019
Messages
11
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!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,921
Messages
5,627,635
Members
416,257
Latest member
salomon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top