Sorting in numerical order when numbers and alphas are used

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 DataText 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 copypaste specialvalues on column C after your sort if you wish.

Gord Dibben Excel MVP

On Fri, 30 Apr 2004 08:22:56 -0700, "Katherine"
wrote:

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

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
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

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