Data sorting problem

[Compass Rose]

Excel 2003

I have a pivot table which draws data from a sheet in my workbook that is
constantly being updated. After I update the data, I update and sort the
pivot table with:

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.Range("K").Select
Selection.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=1, _
Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers

Column K has data in it that looks like this before the sort:

22900400000F-GW
542A0612-13(L)S
2312120000000ME
etc…

After the sort of column K and performing some LEFT, MID and RIGHT functions
on the data, putting the results into columns A to D , some of the data in
column A looks like this:

229
230
2306
2309
231
2312
2313
233
etc…

I would like the data to be sorted like this:

229
230
231
233
..
542
542A
..
2306
2309
2312
2313
etc…

What change to the sort coding do I have to do to have the data in the order
I want?

TIA
David

 

[Gary Brown]

Use the Value( ) function around the info in Col A to make them numbers
instead of text. They will then sort the way you want.

 

[Compass Rose]

Gary, thanks for the response.

The problem is that column A isn't sorted - column K, which is one of the
columns in the pivot table, is the one that is sorted. Column A is populated
by applying a formula to all the rows in column K after it is sorted. The
formula that I use in column A is:
=IF(MID(K1,4,1)="0",LEFT(K1,3),LEFT(K1,4))

How can column K be sorted to give the results I'm looking for?

David