Hi Roger
Yes it was what I wanted, in part. I played around with it using different
preformatted cell conditions. As long as A1 was blank *zzzzz* was returned in
B1(helper Col). But I neglected to remember that A1 would be populated by
*=cell reference to another sheet*. Resulting zero's in A1 caused *#VALUE* to
be returned in B1. Tried various other different variations of formula and
preformatted cell conditions and came up with the below.They all return
*BBBBB* or *ZZZZ* in B1 when A1 is blank or contains a zero, BUT ANY ONE
FORMULA DID NOT WORK UNDER ALL CELL FORMATTING CONDITIONS.

This worked when A1 is preformatted as TEXT
=IF(A1="",REPT("B",6),IF(A1="0",REPT("Z",6),IF(ISN UMBER(--LEFT(A1)),--(LEFT(A1,LEN(A1)-2)),A1)))

This worked when A1 is preformatted as NUMBER or DATE, (though in the DATE
senario the results looked messy when zero in A1, as I normaly have "Zero
Values" deselected it looks OK
=IF(A1="",REPT("B",6),IF(A1=0,REPT("Z",6),IF(ISNUM BER(--LEFT(A1)),--(LEFT(A1,LEN(A1)-2)),A1)))

Apologies for long winded mature of this reply but as an inexperienced Excel
user I have often read posts & replies that are useful / applicable but they
often don't have enough detail for me to implement. In a lot of cases its
assummed the OP knows what they are doing. HA HA. So this is my attempt to
help other newbies in the future.
All I now have to do is figure out how to pick out / delete the data I want
in or from ColA!!!! Have seen possible suggestions posted on this group.

Many thanks for all your help, very much appreciated

Arran



"Roger Govier" wrote:

Hi Arran

Try the following
=IF(A1="",REPT("Z",255),IF(ISNUMBER(--LEFT(A1)),--(LEFT(A1,LEN(A1)-2)),A1))
This will make empty cells have 255 Z's in the helper column, and they
will sort to the end of the list
Text cells will be left exactly as they are.
Is this what you wanted?
If you want the blank cells to the top of the list, then change the
"REPT("Z",255) to 0


--
Regards

Roger Govier


"Arran" wrote in message
...
Hoping this thread is not yet dead as I would like to expand on it.
What changes are required to the formula below, so that when there is
just
text (abcde etc) rather than alpanumeric (25th) in A12 that I get the
desired
"0" returned rather than"#Value" that I am getting.

=IF(A120,--(LEFT(A12,LEN(A12)-2)),0)

Taking this one step further. There will be random blank cells in ColA
so
what would have to be added to the formula so that the sorted list is
displayed from the top of the sorted range down rather than the
bottom up.

Help is always humbly received
"Roger Govier" wrote:

You're very welcome. Thanks for the feedback.

--
Regards

Roger Govier


"Arran" wrote in message
...
Roger, many thanks that worked like a dream.

"Roger Govier" wrote:

Hi Arran

Try using a helper column. In the helper column, enter
=--(LEFT(A1,LEN(A1)-2))
and copy down as far as required.
Mark both columns and sort by the helper column.

--
Regards

Roger Govier


"Arran" wrote in message
...
I cant seem to sort 25th 10th 30th 21st 2nd etc entered data to
display
as
2nd 10th 21st 25th 30th in descending . All cells have been
formated
as text
before data entered. I am using Excel 2003