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
Hi,
Easiest way would be to split the numbers and letters into separate columns.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
format the column as Text.
Add a space at the end to the numbers without a letter.
Sort the column.
Maybe try this:
Put your numbers in Column A and in Column B put this formula:
and drag down.=IF(ISNUMBER(A2),A2&" ",A2)
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
Last edited by abousetta; 11-26-2011 at 08:29 AM. Reason: corrected formula
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
try in b1
=IF(ISNUMBER(--A1),TEXT(A1,"0000"),TEXT(LEFT(A1,LEN(A1)-1),"0000")&RIGHT(A1))
then sort by column b
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks