Re-Post - Data Repeats in Pivot Table

Trying to leave all the data in tact, I used the TRIM formula in the last column, then copied and pasted the values OVER the existing part numbers. For some reason the spaces remained at the end of the part numbers.

You can try it on the attached example, (top of thread), and see if you don't get the same results.

I also tried using replace, selecting the column and replacing a space with nothing, that didn't work either.

Whassup with that?

Thanks for your help, Kumar.

Hutch

By using the code() formula on the last character (the space) of your text, I note that it is code 160.

So to remove it, you could do this:

In a blank column, row 2, type:
=substitute(A2,code(160),"")

Copy that down the whole column, then copy and paste it (as values) over the original values. Then re-run the pivot table.

Regards
Mike

Thanks for the suggestion, Mike. I tried the substitute nothing for the code160 formula, but it didn't remove the spaces. After entering the code in a blank column, and copying it down the column, (the part numbers in the cell containing the formula matched the corresponding part # in column A), I copied only the values over the original part number. I re-ran the pivot table, with the same results. If I check the part numbers in column A, (AFTER pasting the values over them), they still show a blank at the end of the part number.

Any other suggestions?

Thanks in advance.

Hutch