How do I format cells so that room numbers such as 1402, 1405a will sort correctly?

[Dan E]

I'm trying to fix an Excel spreadsheet for a health care facility so that it
can sort data by the first column, whci is the patient's room number. Room
numbers can be pure numerical, or have a suffix at the end (a or b). So I
have e.g. room numbers 1306, 2204, 1401b, 1613, 1402b. At the moment, the
column sorts so that all the room numbers with a suffix follow all the
numbers without a suffix. I've formatted the cells as General, Numbers, or
Text, and get the same sort result each time. How can I fix this? HELP!

All help gratefully received and acknowledged.

 

[Bucky]

I've formatted the cells as General, Numbers, or
Text, and get the same sort result each time.

There is an Excel concept that is tricky to understand. If you enter a
number in a General cell, it gets stored internally as a number (e.g.
1401). If you enter a text in a General cell, it gets stored as a
string (e.g. "1401b"). Once the data is stored internally, it does not
change regardless of what you set the cell format to (General, Number,
Text, etc).

What you want to do here is convert everything to String internally.
The way that I know how to do that is as follows:

1. Make the columns General format. This will allow you to see which
values are stored internally as Number vs String. Number is right
aligned. String is left aligned.

2. In a new cell, say B1, enter the formula B1=TEXT(A1, 0). Fill down.
This converts any numbers to text internally. You would think this
would allow the sort to work correctly now, but for some reason it
doesn't. We need one more step.

3. Select column B. Copy. Move to a new column C. Paste special >
Values. If you did it right, all the values should be left aligned
because they are stored internally as text. And if you have Excel 2003,
you should see little warning symbols "number stored as text". Now you
can sort with this column.

Note that all the columns should be in General format. This is less
confusing.

 

[Bucky]

Once the data is stored internally, it does not
change regardless of what you set the cell format to (General, Number,
Text, etc).

Forgot to mention this for the future:

If you enter a number AFTER the cell format has been set to Text, then
it will be stored as a string internally. So after you do this one time
conversion, set the column format to TEXT. Then all future entries will
be good.

 

[Roger Govier]

Hi Dan

One way
Create a helper column with the formula =--Left(A1,4)
Substitute your column letter for A1. Copy down the column.
Mark the whole block of data but sort by the new helper column.

Regards

Roger Govier

 

[bbuzz]

Be more specific on how you want to sort data. Is the data you want to
retrieve stored horizontally ? I think I can help with a little more
info on the way your data is stored.

bbuzz

 

[Dan E]

Many thanks, Bucky. For general information of the newgroup, a complete
explanation and solution comes up under the topic "Troubleshoot Sorting" in
Excel 2003 Help. Basically, as you say, data entered in a cell that is NOT
formatted as Text BEFORE typing the data doesn't sort as text. You have to
re-type (or use a smart re-entry method such as you describe) AFTER
formatting the cells as Text - then sort works OK. Again, many thanks for
your help.

Dan

 

[Dan E]

Thanks, Roger.

Dan

Hi Dan

One way
Create a helper column with the formula =--Left(A1,4)
Substitute your column letter for A1. Copy down the column.
Mark the whole block of data but sort by the new helper column.

Regards

Roger Govier