An irritating autofilter sort problem.

[MarkVMcCullagh]

Hi
I have a list of alphanumeric part numbers all formatted as text. Some are pure digits while others are a combination. Why does autofilter treat the part numbers with digits as numbers and place them first in the list? Is there any way round this? Please try to keep it simple as I'm not really into VB. Thanks in advance.

 

[Andrew Poulsom]

Annoying isn't it? Formatting the cell as text does not change the underlying value (unless the cell is formatted as text before the value is entered).

Insert a column to the right of your data. In the first cell in your new column enter

=A1&""

replacing A1 with the reference to the first cell in your original list. Copy this formula down as far as needed. Select all the entries in the new column and choose Edit, Copy. Select the first cell in your original list and choose Edit, Paste Special, click Values and press OK. Then delete the new column.

The formula above coerces the entry to text, so the sort will work as you want.

 

[MarkVMcCullagh]

Thanks

I use =A1*1 to convert a text string of digits to numbers but hadnt thought of your solution for converting numbers to text.

 

[Mark W.]

You could have also reparsed the column using the Text to Columns wizard, and in the
process uniformly converted the data type to Text.

 

[Maurizio]

Instead, I use =A1+0 (zero) to convert a text string of digits to numbers.