Sorting in numerical order when numbers and alphas are used

  • Thread starter Thread starter Katherine
  • Start date Start date
K

Katherine

Hi,

We are using an Excel spreadsheet to track our warehouse
inventory at the place where I work. Each location in the
warehouse has a numeric component and an alpha component.
For example:

1-a
1-aa
10-a
10-aa
11-a
11-aa
101-a
101-aa
111-a
111-aa

The problem I am having is that I when I sort by warehouse
location, I get the following sort order:

101-a
101-aa
10-a
10-aa
111-a
111-aa
11-a
11-aa
1-a
1-aa

How can I get Excel to look at only the numbers before the
dash to sort and ignore everything after the dash? I
don't want to split the column into two because I have to
keep the numbers and letters for each location together.
Please help!

Thank you,
Katherine
 
Katherine

One method...

Use Data>Text to Columns to split the data into two columns.

Delimited by -

In column C enter =A1 & "-" & B1

Copy down Column C to end of data.

Select all three columns and do your sort on Column A Ascending.

You can copy>paste special>values on column C after your sort if you wish.

Gord Dibben Excel MVP
 
Katherin,

I agree with Gord. I suspect you'll run into this problem again with this
layout. If you split it into locations and "sub locations" as Gord has
suggested, you can combine them whenever necessary.

=A2 & "-" & B2

Or just have their columns side by side
 
Back
Top