Forum Discussion

pasluy's avatar
pasluy
Copper Contributor
Nov 18, 2020

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

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 wit...
excel
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Nov 19, 2020

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)

    • Wyn Hopkins's avatar
      Wyn Hopkins
      MVP
      Nov 19, 2020
      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

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Nov 19, 2020

      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.

      • pasluy's avatar
        pasluy
        Copper Contributor
        Nov 19, 2020

        Riny_van_Eekelen 

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

Resources