Numbers With Letters Out of Sequence

**llmarsh3** · 11-25-2011, 07:58 AM

I have to import room numbers from a text file into a excel spreadsheet at times. The rooms numbers will at times have letters attached to them. When I sort them they always end up separated from the main number instead of following the that number. For example: if I have room 200, 200A, 200B, 200C, etc. the 200 goes where it suppose to but the 200 with letters ends up towards the bottom of the column. How do I get the room numbers with letters to follow the main number instead of being separated from it? I have to do a cut & paste to get the numbers in order.
Thanks in advance.

LLm

**Domski** · 11-25-2011, 08:10 AM

Hi,

Easiest way would be to split the numbers and letters into separate columns.

Dom

**snb** · 11-25-2011, 12:47 PM

format the column as Text.
Add a space at the end to the numbers without a letter.
Sort the column.

**abousetta** · 11-26-2011, 09:29 AM

Maybe try this:

Put your numbers in Column A and in Column B put this formula:

Please Login or Register  to view this content.

and drag down.

Now highlight both columns and sort by Column B. Excel will recognize that you might have some text and some that look like numbers and give you an option. Pick option #2 (Sort numbers and numbers stores as text separately).

Hope this helps.

abousetta