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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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
687
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,148,022
Messages
5,744,368
Members
423,865
Latest member
SimSum

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