Space Character and Sort Sequence in Excel

[DJB1954]

I have to import from Word a table that needs sorting. The cells in certain columns in the Word table may contain entries like (9) or (a) or (a),(b) or may be blank. I prepare the Excel table by formatting the receiving columns as text. When I paste in the data, I use Paste/Special/text. These two steps are necessary to avoid (9) becoming -9 in Excel.

I sort by columns D and E and then by A, B and C, in both cases in ascending order. I get a result like the following:
Columns A B C D E
Constant Text r 4 (1) (a)
Constant Text r 4 (1) (a)-(d)
Constant Text r 4 (1) (b), (ba)
Constant Text r 4 (1) (c), (d)
Constant Text r 4 (1)
Constant Text r 4 (1)
Constant Text r 4 (1)

However, what I want to achieve is a situation where the last three rows shown are actually the first three becasue, logically (for my application), a blank precedes a lower level of reference detail.

I have tried to use formulae like =IF(ISBLANK(E1),CHAR(255), IF(ISTEXT(E1),E1,TEXT(E1,"@"))) in a helper column, say Column F and replace E with F in the sort instruction. This makes no difference.

Any help would be gratefully appreciated.

DJB1954

 

[PeterMoran]

Hi,

Checkout this site re Special Sort - an Excel add-in. I'm not sure that it does exactly what you want, but you can try for yourself or discuss further at the site:

http://www.realezsites.com/bus/primitivesoftware/products.php

Good Luck!

Peter Moran