Sort serial numbers that contain a letter, by number

[wal]

Excel 2007

Our organization numbers a certain set of items in the following
format:

3B08-####

where 3B is constant, 08 is the year the item was entered (i.e,.
changing from year to year), and #### is a number up to four digits.
#### is written with the appropriate digits without leading zeroes,
e.g.:

3B08-9
3B08-81
3B08-623
3B08-7345

Question: Is there a way to format and Sort these numbers so that the
#### numeric portion is sorted numerically? Using the above four
entries as an example: The only Sort choices are "A to Z", "Z to A"
and "Custom list" (the choice "Smallest to largest" etc. is not
available). With the sort choice "A to Z", the above list will sort
as:

3B08-622
3B08-7333
3B08-81
3B08-9

This results whether the cells are formatted as Text or as Number.

I would like the result to be as shown in the original list, with the
#### numeric portion sorted numerically. Is there any way in Excel to
do this? (I could add leading 0s, but that is cumbersome and not how
we express the numbers on labels, etc.)

Interestingly, when we name files using the above numbers, in list
view Windows will sort the files by "Name" as in the first list above--
i.e., Windows recognizes that numeric quality of the #### portion
notwithstanding the interfering "B" text. So there must be some
underlying code that can do this--but not available in Excel?

Thanks.

 

[Thomas [PBD]]

wal,

You could always create a new column and place the following formula into it
and sort by this column.

=IF(LEN(RIGHT(A1,LEN(A1)-5))=1,A1&"000",IF(LEN(RIGHT(A1,LEN(A1)-5))=2,A1&"00",IF(LEN(RIGHT(A1,LEN(A1)-5))=3,A1&"0",A1)))

In my example, I created the new column in B, where the serial number was in
A.

 

[Thomas [PBD]]

Further simplified:

=IF(LEN(A1)=6,A1&"000",IF(LEN(A1)=7,A1&"00",IF(LEN(A1)=8,A1&"0",A1)))

--
--Thomas [PBD]
Working hard to make working easy.

wal,

You could always create a new column and place the following formula into it
and sort by this column.

=IF(LEN(RIGHT(A1,LEN(A1)-5))=1,A1&"000",IF(LEN(RIGHT(A1,LEN(A1)-5))=2,A1&"00",IF(LEN(RIGHT(A1,LEN(A1)-5))=3,A1&"0",A1)))

In my example, I created the new column in B, where the serial number was in
A.

--
--Thomas [PBD]
Working hard to make working easy.

Excel 2007

Our organization numbers a certain set of items in the following
format:

3B08-####

where 3B is constant, 08 is the year the item was entered (i.e,.
changing from year to year), and #### is a number up to four digits.
#### is written with the appropriate digits without leading zeroes,
e.g.:

3B08-9
3B08-81
3B08-623
3B08-7345

Question: Is there a way to format and Sort these numbers so that the
#### numeric portion is sorted numerically? Using the above four
entries as an example: The only Sort choices are "A to Z", "Z to A"
and "Custom list" (the choice "Smallest to largest" etc. is not
available). With the sort choice "A to Z", the above list will sort
as:

3B08-622
3B08-7333
3B08-81
3B08-9

This results whether the cells are formatted as Text or as Number.

I would like the result to be as shown in the original list, with the
#### numeric portion sorted numerically. Is there any way in Excel to
do this? (I could add leading 0s, but that is cumbersome and not how
we express the numbers on labels, etc.)

Interestingly, when we name files using the above numbers, in list
view Windows will sort the files by "Name" as in the first list above--
i.e., Windows recognizes that numeric quality of the #### portion
notwithstanding the interfering "B" text. So there must be some
underlying code that can do this--but not available in Excel?

Thanks.

 

[RagDyeR]

Use TTC to create a helper column containing only those ending numbers.
Then sort on the helper column.

Insert a new column to the right to receive the parsed numbers.

Select the column of numbers, then, from the Menu Bar:
<Data> <Text To Columns>
Then,
<Delimited> <Next>

Click on "Other", then enter the dash ( - ).

In the "Preview Window", you'll see the data separated.

Click <Next>

In the "Preview Window", you'll see the first column selected by default.
Click on "Do Not Import", and that column header changes to "Skip".

Now, type in the letter of the column you created into the "Destination"
window.
Could be B1, or C5, or wherever you want those numbers to start to display.
This also prevents the original data from being overwritten by the parsed
numbers.

Finally click on <Finish>.

You can now select *all* the columns you wish to include in the sort, and
sort using that "helper" column as the sort key.

You can then delete it to return your data to it's original configuration.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



Excel 2007

Our organization numbers a certain set of items in the following
format:

3B08-####

where 3B is constant, 08 is the year the item was entered (i.e,.
changing from year to year), and #### is a number up to four digits.
#### is written with the appropriate digits without leading zeroes,
e.g.:

3B08-9
3B08-81
3B08-623
3B08-7345

Question: Is there a way to format and Sort these numbers so that the
#### numeric portion is sorted numerically? Using the above four
entries as an example: The only Sort choices are "A to Z", "Z to A"
and "Custom list" (the choice "Smallest to largest" etc. is not
available). With the sort choice "A to Z", the above list will sort
as:

3B08-622
3B08-7333
3B08-81
3B08-9

This results whether the cells are formatted as Text or as Number.

I would like the result to be as shown in the original list, with the
#### numeric portion sorted numerically. Is there any way in Excel to
do this? (I could add leading 0s, but that is cumbersome and not how
we express the numbers on labels, etc.)

Interestingly, when we name files using the above numbers, in list
view Windows will sort the files by "Name" as in the first list above--
i.e., Windows recognizes that numeric quality of the #### portion
notwithstanding the interfering "B" text. So there must be some
underlying code that can do this--but not available in Excel?

Thanks.

 

[wal]

Thanks.

You made them following zeroes (3B08-4000, etc.), but with Left and
Right I can insert the zeroes after the hyphen.

wal,

You could always create a new column and place the following formula intoit
and sort by this column.

=IF(LEN(RIGHT(A1,LEN(A1)-5))=1,A1&"000",IF(LEN(RIGHT(A1,LEN(A1)-5))=2,A1&"0­0",IF(LEN(RIGHT(A1,LEN(A1)-5))=3,A1&"0",A1)))

In my example, I created the new column in B, where the serial number wasin
A.

--
--Thomas [PBD]
Working hard to make working easy.

Excel 2007

Our organization numbers a certain set of items in the following
format:

where 3B is constant, 08 is the year the item was entered (i.e,.
changing from year to year), and #### is a number up to four digits.
#### is written with the appropriate digits without leading zeroes,
e.g.:

Question: Is there a way to format and Sort these numbers so that the
#### numeric portion is sorted numerically?  Using the above four
entries as an example: The only Sort choices are "A to Z", "Z to A"
and "Custom list" (the choice "Smallest to largest" etc. is not
available).  With the sort choice "A to Z", the above list will sort
as:

This results whether the cells are formatted as Text or as Number.

I would like the result to be as shown in the original list, with the
#### numeric portion sorted numerically.  Is there any way in Excel to
do this?  (I could add leading 0s, but that is cumbersome and not how
we express the numbers on labels, etc.)

Interestingly, when we name files using the above numbers, in list
view Windows will sort the files by "Name" as in the first list above--
i.e., Windows recognizes that numeric quality of the #### portion
notwithstanding the interfering "B" text.  So there must be some
underlying code that can do this--but not available in Excel?

Thanks.- Hide quoted text -

- Show quoted text -

 

[Bernie Deitrick]

Use

=VALUE(MID(A2,6,4))

as your helper column of formulas, and sort on them.

HTH,
Bernie
MS Excel MVP

 

[Thomas [PBD]]

wal,

I'm very sorry, you are correct. I did not answer the initial problem.
Bernie Deitrick's solution should probably work for you. Helper column:
=VALUE(MID(A1,6,4)) and sort on that criteria.

--
--Thomas [PBD]
Working hard to make working easy.

Thanks.

You made them following zeroes (3B08-4000, etc.), but with Left and
Right I can insert the zeroes after the hyphen.

wal,

You could always create a new column and place the following formula into it
and sort by this column.

=IF(LEN(RIGHT(A1,LEN(A1)-5))=1,A1&"000",IF(LEN(RIGHT(A1,LEN(A1)-5))=2,A1&"0­0",IF(LEN(RIGHT(A1,LEN(A1)-5))=3,A1&"0",A1)))

In my example, I created the new column in B, where the serial number was in
A.

--
--Thomas [PBD]
Working hard to make working easy.

Excel 2007

Our organization numbers a certain set of items in the following
format:

where 3B is constant, 08 is the year the item was entered (i.e,.
changing from year to year), and #### is a number up to four digits.
#### is written with the appropriate digits without leading zeroes,
e.g.:

Question: Is there a way to format and Sort these numbers so that the
#### numeric portion is sorted numerically? Using the above four
entries as an example: The only Sort choices are "A to Z", "Z to A"
and "Custom list" (the choice "Smallest to largest" etc. is not
available). With the sort choice "A to Z", the above list will sort
as:

This results whether the cells are formatted as Text or as Number.

I would like the result to be as shown in the original list, with the
#### numeric portion sorted numerically. Is there any way in Excel to
do this? (I could add leading 0s, but that is cumbersome and not how
we express the numbers on labels, etc.)

Interestingly, when we name files using the above numbers, in list
view Windows will sort the files by "Name" as in the first list above--
i.e., Windows recognizes that numeric quality of the #### portion
notwithstanding the interfering "B" text. So there must be some
underlying code that can do this--but not available in Excel?

Thanks.- Hide quoted text -

- Show quoted text -

 

[Bob Umlas]

Helper column:
=1*MID(A1,6,100)
and fill down
Bob Umlas
Excel MVP