Text is not Text in Excel even the cell format is text

[pasluy]

By copy data from another excelsheet i have a problem that, even the cells is text format Excel see it is not the same format. So if you make a pivot table de data gives an other position. I copy with copy values it is not the solution. Olso i change the cell format for the wrong cells in text format but is olso not the solution. Only by open the cell and close the cell by type F2 and then Enter the text will be equal to the other text cells. I have to much rules to open every cell by hand.

Do enyone knows how to fix it?

[#2]

@pasluy 

With your permission, I'm not an expert at Pivot.

But I know (even if I don't know anything) if you insert a file (without sensitive data) and explain your problem on this file, you will get an answer faster.

Notification of the Excel version and operating system urge you to get a precise answer.

Thank you for your patience and time.

Nikolino

I know that I don't know (Socrates)

[#3]

@NikolinoDE  Yes now attached the file

[#4]

@pasluy Select the range with the account numbers in column C. Then, on the Data ribbon, select "Text to columns". In step 3 of the wizard, select General as the data format and press Finish. Now, all the account numbers will have become real numbers. Refresh the pivot table and all entries on account 4011 will be grouped as expected.

[#5]

If you want to convert them all to numbers then a quick way is to highlight all the numbers (don't including the heading) and then choose Data > Text To Columns > Finish

If you need them all to be text then it's more difficult, potentially adding a calculation column =Text(C5, "@") and copying that down

[#6]

@Riny_van_Eekelen 

Thanks but i try to keep it text even if it is a number.

[#7]

@pasluy Well, then choose Text in the third step to make all of them text. I believe your problem was that for two of the entries the format of "4100" was not consistent with the "4100" for the other entries. That'w why the pivot table showed two rows with "4100". Using  "Text to columns" forces all "numbers" in the column to one consistent data type. Be it a number or text. That's your choice.

[#8]

@Riny_van_Eekelen 

Just to illustrate