I have the following data:

S12345
S12346
12347
12348

I would like to sort it in ascending numerical order, omitting the
leading 'S'. Is there anyway to do this?

Many Thanks,

Jason R. Kaiser Sr.

You will need to separate the numeric part into a "helper" column and then
sort.

If data always has 5 digits as per your sample then

=Right(A1,5) will extract the numeric part. Copy down and sort on this column.

HTH

" wrote:

I have the following data:

S12345
S12346
12347
12348

I would like to sort it in ascending numerical order, omitting the
leading 'S'. Is there anyway to do this?

Many Thanks,

Jason R. Kaiser Sr.

Hi,

try to use an auxiliar column with =mid(a2,1,255) copy it down and order by
column B.

hth
regards from Brazil
Marcelo

" escreveu:

I have the following data:

S12345
S12346
12347
12348

I would like to sort it in ascending numerical order, omitting the
leading 'S'. Is there anyway to do this?

Many Thanks,

Jason R. Kaiser Sr.

Hi Jason,

In your simplified version all you need is to insert a helper
column next to your data and sort on that column.

i.e. if your data is in A1 down, in B1 put =RIGHT(A1,5)
and copy down to the end of your data and sort on column B.

If there is more variation in your data i.e. more or less digits
or whatever, then you will need a different approach.

HTH
Martin

if you have only S as first character of some of your data and rest of
numbers are not fixed then in col B (if your data starts from cell A1),
enter the following function in B1
=IF(LEFT(A1,1)="S",MID(A1,2,10),A1) and copy it down, it will extrat
only numbers from your data. Then select col B and copy it, without
deselecting, right click on the selection area, click paste special and
click values then ok. Now select col A and B and sort by col B in
ascending order. delete values from col B. you data is sorted in
ascending order as per numbers.

hope this would help you.

Wrote:
I have the following data:

S12345
S12346
12347
12348

I would like to sort it in ascending numerical order, omitting the
leading 'S'. Is there anyway to do this?

Many Thanks,

Jason R. Kaiser Sr.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=562046

Thanks for the help all! It worked great :) I'm a satisfied
customer....

Jason

wrote:
I have the following data:

S12345
S12346
12347
12348

I would like to sort it in ascending numerical order, omitting the
leading 'S'. Is there anyway to do this?

Many Thanks,

Jason R. Kaiser Sr.