How to change Excel Pivot table column to text format for vlookup purposes

Question

I have a column in my data that is ID numbers, basically. Made a pivot table out of the data. I'm trying to do a vlookup into the pivot that ignores the first number in the data like so:

=vlookup("*"&A1,pivot table columns,2,false)

But I get N/A because the *&A1 is considered text, which I cannot change, and the pivot table data is considered to be numbers. I've tried going to the original data and changing the formatting to text and refreshing, I've tried formatting the cells where the data appears and I've tried going into field options and changed the number formatting to text. None of it worked.

Anyone know an easy way to get this to work?

Solution

Force your ID number to be treated as text in the source data.

To do this reliably:

1. select the column in your data source with ID number
2. Data->Text-To-Columns
3. Next
4. Next
5. Choose Text
6. Finish
7. Refresh the Pivot.

Comments

[CharlieRB]

Changing the format of a column doesn't always change the contents. You may have to change the table data type by using Text to Column and choosing the Text format.

[Matt]

Thanks! That worked. I forget about that text to columns function.