An irritating autofilter sort problem.

MarkVMcCullagh

Board Regular
Joined
Oct 22, 2002
Messages
72
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Oct 22, 2002
Messages
72
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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

Well-known Member
Joined
Oct 15, 2002
Messages
682
Office Version
  1. 2007
Platform
  1. Windows
Instead, I use =A1+0 (zero) to convert a text string of digits to numbers.
 

Forum statistics

Threads
1,144,287
Messages
5,723,512
Members
422,501
Latest member
Tausyef

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top