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

Question

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?

nikolinode · Answer

pasluy&nbsp;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. &nbsp;Thank you for your patience and time. &nbsp;Nikolino I know that I don't know (Socrates)

pasluy · Answer

NikolinoDE&nbsp; Yes now attached the file

riny_van_eekelen · Answer

pasluy&nbsp;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.

wyn hopkins · Answer

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

pasluy · Answer

Riny_van_Eekelen&nbsp;Thanks but i try to keep it text even if it is a number.

Forum Discussion

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

7 Replies

Resources