| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
sort cells with hyphen #####-###-####
How do you sort a column of numbers with hyphens?
Example #####-###-####. I want to sort by the 3 middle numbers Thanks |
|
#2
|
|||
|
|||
|
You can extract the 3 middle characters to a new column and then sort on that
new column. =MID(A1,7,3) HTH Jason Atlanta, GA "bagman" wrote: How do you sort a column of numbers with hyphens? Example #####-###-####. I want to sort by the 3 middle numbers Thanks |
|
#3
|
|||
|
|||
|
Put the middle numbers into a separate column and sort by that.
This formula will get you those values =LEFT(MID(A1,FIND("-",A1)+1,99),FIND("-",MID(A1,FIND("-",A1)+1,99))-1) -- HTH RP (remove nothere from the email address if mailing direct) "bagman" wrote in message ... How do you sort a column of numbers with hyphens? Example #####-###-####. I want to sort by the 3 middle numbers Thanks |
|
#4
|
|||
|
|||
|
Hello Jason,
Thanks..But I am not that good with excel..so could you explain how to extract the by the three middle numbers and sort the complete number in a new column. Sorry for all the questions. "Jason Morin" wrote: You can extract the 3 middle characters to a new column and then sort on that new column. =MID(A1,7,3) HTH Jason Atlanta, GA "bagman" wrote: How do you sort a column of numbers with hyphens? Example #####-###-####. I want to sort by the 3 middle numbers Thanks |
|
#5
|
|||
|
|||
|
Hello Bob,
As I told Jason I am not very good with excel..I got your formula. But how and where to I enter it get the column to sort in a new column. Thanks "bagman" wrote: How do you sort a column of numbers with hyphens? Example #####-###-####. I want to sort by the 3 middle numbers Thanks |
|
#6
|
|||
|
|||
|
Bob's formula
=LEFT(MID(A1,FIND("-",A1)+1,99),FIND("-",MID(A1,FIND("-",A1)+1,99))-1) would assume your data is in column A. So this would be placed in cell B1, then copied down next to each entry in column A. -- Regards, Tom Ogilvy "bagman" wrote in message ... Hello Bob, As I told Jason I am not very good with excel..I got your formula. But how and where to I enter it get the column to sort in a new column. Thanks "bagman" wrote: How do you sort a column of numbers with hyphens? Example #####-###-####. I want to sort by the 3 middle numbers Thanks |
|
#7
|
|||
|
|||
|
Thanks Tom, Bob and Jason,
That worked... "Tom Ogilvy" wrote: Bob's formula =LEFT(MID(A1,FIND("-",A1)+1,99),FIND("-",MID(A1,FIND("-",A1)+1,99))-1) would assume your data is in column A. So this would be placed in cell B1, then copied down next to each entry in column A. -- Regards, Tom Ogilvy "bagman" wrote in message ... Hello Bob, As I told Jason I am not very good with excel..I got your formula. But how and where to I enter it get the column to sort in a new column. Thanks "bagman" wrote: How do you sort a column of numbers with hyphens? Example #####-###-####. I want to sort by the 3 middle numbers Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sort cells with hyphen #####-###-#### | Excel Discussion (Misc queries) | |||
| To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) | |||
| Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) | |||
| Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
| Data > Sort function amnesia? | Excel Discussion (Misc queries) | |||
Linear Mode
