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

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

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

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

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

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

Unbelievably qiuck reply Roger, thank you. I will play around with it this
evening( being in 8-10 hrs)
Many thanks

"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

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

Hi Arran

Thank you for responding and giving details of what you tried, and the
results you achieved.

If we amend the formula slightly, and make the test for 0 different, one
formula should serve all cases.
Test for the ASCII code value of the cell being 48 (code for 0) then it
will deal with Text zero or Numeric 0

=IF(A1="",REPT("B",6),
IF(CODE(LEFT(A1))=48,
REPT("Z",6),IF(AND(LEN(A1)=4,ISNUMBER(--LEFT(A1))),
--(LEFT(A1,LEN(A1)-2)),A1)))

I also added an AND to the test of whether the value in cell A1 started
with a number to ensure it was 4 characters in length to deal only with
ordinals (where we first started) and not to fail if there is a
straightforward number other than 0 in the cell.


--
Regards

Roger Govier


"Arran" wrote in message
...
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

Hello again Roger

Interesting things happened when I entered you last version. The changes for
text or numeric zeros work seamlessly. With *AND*when true set at 4, any data
entered in A1 that was 1st, 2nd etc through to 9th, returned in B1 with A1
format eg 1st 2nd through 9th & was left alined. But any thing higher in
A1(10th - 31st) returned in B1 just 10 -31 & was right alined. Interestingly
when I changed the *AND* when true to 3 the reverse happened. When changed
to 2 everything was suffixed as they where entered in A1 and left alined. Was
this what you expcected?

Regards
Arran

"Roger Govier" wrote:

Hi Arran

Thank you for responding and giving details of what you tried, and the
results you achieved.

If we amend the formula slightly, and make the test for 0 different, one
formula should serve all cases.
Test for the ASCII code value of the cell being 48 (code for 0) then it
will deal with Text zero or Numeric 0

=IF(A1="",REPT("B",6),
IF(CODE(LEFT(A1))=48,
REPT("Z",6),IF(AND(LEN(A1)=4,ISNUMBER(--LEFT(A1))),
--(LEFT(A1,LEN(A1)-2)),A1)))

I also added an AND to the test of whether the value in cell A1 started
with a number to ensure it was 4 characters in length to deal only with
ordinals (where we first started) and not to fail if there is a
straightforward number other than 0 in the cell.


--
Regards

Roger Govier


"Arran" wrote in message
...
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

Hi Arran

Bad thinking on my part,
Change =4 to 2.


--
Regards

Roger Govier


"Arran" wrote in message
...
Hello again Roger

Interesting things happened when I entered you last version. The
changes for
text or numeric zeros work seamlessly. With *AND*when true set at 4,
any data
entered in A1 that was 1st, 2nd etc through to 9th, returned in B1
with A1
format eg 1st 2nd through 9th & was left alined. But any thing higher
in
A1(10th - 31st) returned in B1 just 10 -31 & was right alined.
Interestingly
when I changed the *AND* when true to 3 the reverse happened. When
changed
to 2 everything was suffixed as they where entered in A1 and left
alined. Was
this what you expcected?

Regards
Arran

"Roger Govier" wrote:

Hi Arran

Thank you for responding and giving details of what you tried, and
the
results you achieved.

If we amend the formula slightly, and make the test for 0 different,
one
formula should serve all cases.
Test for the ASCII code value of the cell being 48 (code for 0) then
it
will deal with Text zero or Numeric 0

=IF(A1="",REPT("B",6),
IF(CODE(LEFT(A1))=48,
REPT("Z",6),IF(AND(LEN(A1)=4,ISNUMBER(--LEFT(A1))),
--(LEFT(A1,LEN(A1)-2)),A1)))

I also added an AND to the test of whether the value in cell A1
started
with a number to ensure it was 4 characters in length to deal only
with
ordinals (where we first started) and not to fail if there is a
straightforward number other than 0 in the cell.


--
Regards

Roger Govier


"Arran" wrote in message
...
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

Hi Roger

Changed =4 to 2 as you advised. Resulted in A1 entered data suffixed with
*st, nd or th* being returned in B1 as plain numeric numbers & plain numeric
numbers in A1 returned as plain numeric in B1 and not *# Value* as you
expected.
But I inadvertantly typed *2<2* at one point which resulted in all returns
in B1 being an exact match as data entered in A1 (1st = 1st, 10th=10th, 1=1,
ab=ab etc). Tried it with *2<0,1,2,3,4 and 5, results all ways the same.
What exactly is the *2<?* argument doing as it seems an illogical to me.

Arran
"Roger Govier" wrote:

Hi Arran

Bad thinking on my part,
Change =4 to 2.


--
Regards

Roger Govier


"Arran" wrote in message
...
Hello again Roger

Interesting things happened when I entered you last version. The
changes for
text or numeric zeros work seamlessly. With *AND*when true set at 4,
any data
entered in A1 that was 1st, 2nd etc through to 9th, returned in B1
with A1
format eg 1st 2nd through 9th & was left alined. But any thing higher
in
A1(10th - 31st) returned in B1 just 10 -31 & was right alined.
Interestingly
when I changed the *AND* when true to 3 the reverse happened. When
changed
to 2 everything was suffixed as they where entered in A1 and left
alined. Was
this what you expcected?

Regards
Arran

"Roger Govier" wrote:

Hi Arran

Thank you for responding and giving details of what you tried, and
the
results you achieved.

If we amend the formula slightly, and make the test for 0 different,
one
formula should serve all cases.
Test for the ASCII code value of the cell being 48 (code for 0) then
it
will deal with Text zero or Numeric 0

=IF(A1="",REPT("B",6),
IF(CODE(LEFT(A1))=48,
REPT("Z",6),IF(AND(LEN(A1)=4,ISNUMBER(--LEFT(A1))),
--(LEFT(A1,LEN(A1)-2)),A1)))

I also added an AND to the test of whether the value in cell A1
started
with a number to ensure it was 4 characters in length to deal only
with
ordinals (where we first started) and not to fail if there is a
straightforward number other than 0 in the cell.


--
Regards

Roger Govier


"Arran" wrote in message
...
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

Hi Arran

What exactly is the *2<?* argument doing as it seems an illogical to
me

I don't know what you mean by *2<*
All said was swap =4 for 2.

The reason is, if you had entered a number like 1 or 23 in the cell,
then because they are less than 3 characters, the term
--(LEFT(A1,LEN(A1)-2))

would fail, as it would be trying to take string of 0 or negative
characters

The amended formula is as below.

=IF(A1="",REPT("B",6),
IF(CODE(LEFT(A1))=48,
REPT("Z",6),IF(AND(LEN(A1)2,ISNUMBER(--LEFT(A1))),
--(LEFT(A1,LEN(A1)-2)),A1)))


--
Regards

Roger Govier


"Arran" wrote in message
...
Hi Roger

Changed =4 to 2 as you advised. Resulted in A1 entered data suffixed
with
*st, nd or th* being returned in B1 as plain numeric numbers & plain
numeric
numbers in A1 returned as plain numeric in B1 and not *# Value* as you
expected.
But I inadvertantly typed *2<2* at one point which resulted in all
returns
in B1 being an exact match as data entered in A1 (1st = 1st,
10th=10th, 1=1,
ab=ab etc). Tried it with *2<0,1,2,3,4 and 5, results all ways the
same.
What exactly is the *2<?* argument doing as it seems an illogical to
me.

Arran
"Roger Govier" wrote:

Hi Arran

Bad thinking on my part,
Change =4 to 2.


--
Regards

Roger Govier


"Arran" wrote in message
...
Hello again Roger

Interesting things happened when I entered you last version. The
changes for
text or numeric zeros work seamlessly. With *AND*when true set at
4,
any data
entered in A1 that was 1st, 2nd etc through to 9th, returned in B1
with A1
format eg 1st 2nd through 9th & was left alined. But any thing
higher
in
A1(10th - 31st) returned in B1 just 10 -31 & was right alined.
Interestingly
when I changed the *AND* when true to 3 the reverse happened. When
changed
to 2 everything was suffixed as they where entered in A1 and left
alined. Was
this what you expcected?

Regards
Arran

"Roger Govier" wrote:

Hi Arran

Thank you for responding and giving details of what you tried, and
the
results you achieved.

If we amend the formula slightly, and make the test for 0
different,
one
formula should serve all cases.
Test for the ASCII code value of the cell being 48 (code for 0)
then
it
will deal with Text zero or Numeric 0

=IF(A1="",REPT("B",6),
IF(CODE(LEFT(A1))=48,
REPT("Z",6),IF(AND(LEN(A1)=4,ISNUMBER(--LEFT(A1))),
--(LEFT(A1,LEN(A1)-2)),A1)))

I also added an AND to the test of whether the value in cell A1
started
with a number to ensure it was 4 characters in length to deal only
with
ordinals (where we first started) and not to fail if there is a
straightforward number other than 0 in the cell.


--
Regards

Roger Govier


"Arran" wrote in message
...
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

Hi Roger
No it wasnt very clear was it now I look again.
By accident I replaced =4 with 2<2 instead of simply 2.
When I used simply 2, data entered in A1 as 1st, 22nd, 25th etc returned in
B1 as, 1, 22, 25. But 2<2 returned in B1, 1st, 22nd, 25th. It is the effect
the added <2 was have that I was curious about. To me 2<2 (greater than 2
less than 2) could be replaced with 1 and return the same result but it does
not.
Hope this makes sense

Arran

"Roger Govier" wrote:

Hi Arran

What exactly is the *2<?* argument doing as it seems an illogical to
me

I don't know what you mean by *2<*
All said was swap =4 for 2.

The reason is, if you had entered a number like 1 or 23 in the cell,
then because they are less than 3 characters, the term
--(LEFT(A1,LEN(A1)-2))

would fail, as it would be trying to take string of 0 or negative
characters

The amended formula is as below.

=IF(A1="",REPT("B",6),
IF(CODE(LEFT(A1))=48,
REPT("Z",6),IF(AND(LEN(A1)2,ISNUMBER(--LEFT(A1))),
--(LEFT(A1,LEN(A1)-2)),A1)))


--
Regards

Roger Govier


"Arran" wrote in message
...
Hi Roger

Changed =4 to 2 as you advised. Resulted in A1 entered data suffixed
with
*st, nd or th* being returned in B1 as plain numeric numbers & plain
numeric
numbers in A1 returned as plain numeric in B1 and not *# Value* as you
expected.
But I inadvertantly typed *2<2* at one point which resulted in all
returns
in B1 being an exact match as data entered in A1 (1st = 1st,
10th=10th, 1=1,
ab=ab etc). Tried it with *2<0,1,2,3,4 and 5, results all ways the
same.
What exactly is the *2<?* argument doing as it seems an illogical to
me.

Arran
"Roger Govier" wrote:

Hi Arran

Bad thinking on my part,
Change =4 to 2.


--
Regards

Roger Govier


"Arran" wrote in message
...
Hello again Roger

Interesting things happened when I entered you last version. The
changes for
text or numeric zeros work seamlessly. With *AND*when true set at
4,
any data
entered in A1 that was 1st, 2nd etc through to 9th, returned in B1
with A1
format eg 1st 2nd through 9th & was left alined. But any thing
higher
in
A1(10th - 31st) returned in B1 just 10 -31 & was right alined.
Interestingly
when I changed the *AND* when true to 3 the reverse happened. When
changed
to 2 everything was suffixed as they where entered in A1 and left
alined. Was
this what you expcected?

Regards
Arran

"Roger Govier" wrote:

Hi Arran

Thank you for responding and giving details of what you tried, and
the
results you achieved.

If we amend the formula slightly, and make the test for 0
different,
one
formula should serve all cases.
Test for the ASCII code value of the cell being 48 (code for 0)
then
it
will deal with Text zero or Numeric 0

=IF(A1="",REPT("B",6),
IF(CODE(LEFT(A1))=48,
REPT("Z",6),IF(AND(LEN(A1)=4,ISNUMBER(--LEFT(A1))),
--(LEFT(A1,LEN(A1)-2)),A1)))

I also added an AND to the test of whether the value in cell A1
started
with a number to ensure it was 4 characters in length to deal only
with
ordinals (where we first started) and not to fail if there is a
straightforward number other than 0 in the cell.


--
Regards

Roger Govier


"Arran" wrote in message
...
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

Hi Arran

Because you put 2<2 the third IF fails with a False result, thereby
returning the last term in the expression, which is to take the contents
of A1

--
Regards

Roger Govier


"Arran" wrote in message
...
Hi Roger
No it wasnt very clear was it now I look again.
By accident I replaced =4 with 2<2 instead of simply 2.
When I used simply 2, data entered in A1 as 1st, 22nd, 25th etc
returned in
B1 as, 1, 22, 25. But 2<2 returned in B1, 1st, 22nd, 25th. It is the
effect
the added <2 was have that I was curious about. To me 2<2 (greater
than 2
less than 2) could be replaced with 1 and return the same result but
it does
not.
Hope this makes sense

Arran

"Roger Govier" wrote:

Hi Arran

What exactly is the *2<?* argument doing as it seems an illogical
to
me

I don't know what you mean by *2<*
All said was swap =4 for 2.

The reason is, if you had entered a number like 1 or 23 in the cell,
then because they are less than 3 characters, the term
--(LEFT(A1,LEN(A1)-2))

would fail, as it would be trying to take string of 0 or negative
characters

The amended formula is as below.

=IF(A1="",REPT("B",6),
IF(CODE(LEFT(A1))=48,
REPT("Z",6),IF(AND(LEN(A1)2,ISNUMBER(--LEFT(A1))),
--(LEFT(A1,LEN(A1)-2)),A1)))


--
Regards

Roger Govier


"Arran" wrote in message
...
Hi Roger

Changed =4 to 2 as you advised. Resulted in A1 entered data
suffixed
with
*st, nd or th* being returned in B1 as plain numeric numbers &
plain
numeric
numbers in A1 returned as plain numeric in B1 and not *# Value* as
you
expected.
But I inadvertantly typed *2<2* at one point which resulted in all
returns
in B1 being an exact match as data entered in A1 (1st = 1st,
10th=10th, 1=1,
ab=ab etc). Tried it with *2<0,1,2,3,4 and 5, results all ways the
same.
What exactly is the *2<?* argument doing as it seems an illogical
to
me.

Arran
"Roger Govier" wrote:

Hi Arran

Bad thinking on my part,
Change =4 to 2.


--
Regards

Roger Govier


"Arran" wrote in message
...
Hello again Roger

Interesting things happened when I entered you last version. The
changes for
text or numeric zeros work seamlessly. With *AND*when true set
at
4,
any data
entered in A1 that was 1st, 2nd etc through to 9th, returned in
B1
with A1
format eg 1st 2nd through 9th & was left alined. But any thing
higher
in
A1(10th - 31st) returned in B1 just 10 -31 & was right alined.
Interestingly
when I changed the *AND* when true to 3 the reverse happened.
When
changed
to 2 everything was suffixed as they where entered in A1 and
left
alined. Was
this what you expcected?

Regards
Arran

"Roger Govier" wrote:

Hi Arran

Thank you for responding and giving details of what you tried,
and
the
results you achieved.

If we amend the formula slightly, and make the test for 0
different,
one
formula should serve all cases.
Test for the ASCII code value of the cell being 48 (code for 0)
then
it
will deal with Text zero or Numeric 0

=IF(A1="",REPT("B",6),
IF(CODE(LEFT(A1))=48,
REPT("Z",6),IF(AND(LEN(A1)=4,ISNUMBER(--LEFT(A1))),
--(LEFT(A1,LEN(A1)-2)),A1)))

I also added an AND to the test of whether the value in cell A1
started
with a number to ensure it was 4 characters in length to deal
only
with
ordinals (where we first started) and not to fail if there is a
straightforward number other than 0 in the cell.


--
Regards

Roger Govier


"Arran" wrote in message
...
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

Hi Roger

Thanks for that, just thought it was an interesting out come.
I will leave it there!!

Many many thanks for all your help I am gratefull for your time.

Regards

Arran



"Roger Govier" wrote:

Hi Arran

Because you put 2<2 the third IF fails with a False result, thereby
returning the last term in the expression, which is to take the contents
of A1

--
Regards

Roger Govier


"Arran" wrote in message
...
Hi Roger
No it wasnt very clear was it now I look again.
By accident I replaced =4 with 2<2 instead of simply 2.
When I used simply 2, data entered in A1 as 1st, 22nd, 25th etc
returned in
B1 as, 1, 22, 25. But 2<2 returned in B1, 1st, 22nd, 25th. It is the
effect
the added <2 was have that I was curious about. To me 2<2 (greater
than 2
less than 2) could be replaced with 1 and return the same result but
it does
not.
Hope this makes sense

Arran

"Roger Govier" wrote:

Hi Arran

What exactly is the *2<?* argument doing as it seems an illogical
to
me

I don't know what you mean by *2<*
All said was swap =4 for 2.

The reason is, if you had entered a number like 1 or 23 in the cell,
then because they are less than 3 characters, the term
--(LEFT(A1,LEN(A1)-2))

would fail, as it would be trying to take string of 0 or negative
characters

The amended formula is as below.

=IF(A1="",REPT("B",6),
IF(CODE(LEFT(A1))=48,
REPT("Z",6),IF(AND(LEN(A1)2,ISNUMBER(--LEFT(A1))),
--(LEFT(A1,LEN(A1)-2)),A1)))


--
Regards

Roger Govier


"Arran" wrote in message
...
Hi Roger

Changed =4 to 2 as you advised. Resulted in A1 entered data
suffixed
with
*st, nd or th* being returned in B1 as plain numeric numbers &
plain
numeric
numbers in A1 returned as plain numeric in B1 and not *# Value* as
you
expected.
But I inadvertantly typed *2<2* at one point which resulted in all
returns
in B1 being an exact match as data entered in A1 (1st = 1st,
10th=10th, 1=1,
ab=ab etc). Tried it with *2<0,1,2,3,4 and 5, results all ways the
same.
What exactly is the *2<?* argument doing as it seems an illogical
to
me.

Arran
"Roger Govier" wrote:

Hi Arran

Bad thinking on my part,
Change =4 to 2.


--
Regards

Roger Govier


"Arran" wrote in message
...
Hello again Roger

Interesting things happened when I entered you last version. The
changes for
text or numeric zeros work seamlessly. With *AND*when true set
at
4,
any data
entered in A1 that was 1st, 2nd etc through to 9th, returned in
B1
with A1
format eg 1st 2nd through 9th & was left alined. But any thing
higher
in
A1(10th - 31st) returned in B1 just 10 -31 & was right alined.
Interestingly
when I changed the *AND* when true to 3 the reverse happened.
When
changed
to 2 everything was suffixed as they where entered in A1 and
left
alined. Was
this what you expcected?

Regards
Arran

"Roger Govier" wrote:

Hi Arran

Thank you for responding and giving details of what you tried,
and
the
results you achieved.

If we amend the formula slightly, and make the test for 0
different,
one
formula should serve all cases.
Test for the ASCII code value of the cell being 48 (code for 0)
then
it
will deal with Text zero or Numeric 0

=IF(A1="",REPT("B",6),
IF(CODE(LEFT(A1))=48,
REPT("Z",6),IF(AND(LEN(A1)=4,ISNUMBER(--LEFT(A1))),
--(LEFT(A1,LEN(A1)-2)),A1)))

I also added an AND to the test of whether the value in cell A1
started
with a number to ensure it was 4 characters in length to deal
only
with
ordinals (where we first started) and not to fail if there is a
straightforward number other than 0 in the cell.


--
Regards

Roger Govier


"Arran" wrote in message
...
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: