Sorting '# 9" numberically instead of alphbetically

[Ken Loomis]

I am trying to sort an Excel worksheet that contains (among other things)
columns for:

name
street number
street name
unit number

I am sorting on the unit number first, then the street number and finally
the street name to group the streets and put all the addresses for that
street in order.

However, the unit numbers look like this:

# 1
# 2
# 3
# 4
# 5
# 7
# 8
# 10
# 12
# 13
# 14
# 15
# 16
# 9

So that unit # 9 appears at the end of this sorted list instead of coming
between # 8 and # 10.

The only way I can think of to correct this would be to strip out the '#'
and the space, sort the spreadsheet and then add the '#' and space back in.

Is there some other way to do that?

And, if that is what I need to do, what would be an efficient way to remove
the '# ' and then add it back in front of the unit number? There are some
addresses that do not have unit numbers.

Thanks,
Ken Loomis

 

[Steve Smallman]

Ken,

It is possible to format the cells with a custom format ("#" #) which will
allow you to enter the numbers as numbers, and display the unit number as "#
9". Once you have entered the numbers, you can then numerically sort them

Next problem is using them. If you are doing a mail merge in word, you will
only receive the data, and can then use the if functionality in word to
insert your # symbol.

Steve

 

[Ken Loomis]

Thanks, Steve.

All I need to do is print that report from the Excel spreadsheet, so your
solution is very nice.

Ken Loomis