In Excel, can I sort a column of 12 digit numbers by the sixth digit?
January 7, 2005 4:11 AM Subscribe
EXCEL FILTER: I have an entire column of 12 digit numbers with dashes (X-XXXX-XXXXX-X-X) and I need to delete the first five digits (and two dashes) so I can sort the numbers starting with the sixth digit. Is this possible? (more inside)
The longer number (X-XXXX-XXXXX-X-X) is a product SKU number and I need to be able sort them by selection number (XXXXX-X). The column has over 9,000 lines (and grows by a few hundred every month) so I can't do it manually. I once dealt with an account that used to do this with the files I sent them, but now I have to do it myself and I don't have that contact anymore. I've tried Excel's HELP, AskMeFi archives, an Excel blog, and various Google searches and can't find an answer no matter how I try to phrase what I'm trying to do. I'm using Excel 2000. It sounds like a Macro thing, but I'm not sure exactly how.
The longer number (X-XXXX-XXXXX-X-X) is a product SKU number and I need to be able sort them by selection number (XXXXX-X). The column has over 9,000 lines (and grows by a few hundred every month) so I can't do it manually. I once dealt with an account that used to do this with the files I sent them, but now I have to do it myself and I don't have that contact anymore. I've tried Excel's HELP, AskMeFi archives, an Excel blog, and various Google searches and can't find an answer no matter how I try to phrase what I'm trying to do. I'm using Excel 2000. It sounds like a Macro thing, but I'm not sure exactly how.
There's probably dozens of ways to do this, each better than the next. I'd suggest you split parts of the numbers in to separate columns.
Select the column with your numbers. Choose Menu>Data>Text_to_Columns. Select the radio button for fixed width. Create break lines as desired. Click Finish. Sort away.
posted by klarck at 4:35 AM on January 7, 2005
Select the column with your numbers. Choose Menu>Data>Text_to_Columns. Select the radio button for fixed width. Create break lines as desired. Click Finish. Sort away.
posted by klarck at 4:35 AM on January 7, 2005
Response by poster: Well I'll be damned Klarck. That's it exactly. I spent hours reading through the HELP section and doing Google searches and a co-worker and I trial and errored it for quite a while and you just nailed it it in under 25 minutes. Thanks, you're option suits my needs even better than what I was actually looking for - I didn't even know your option existed. Plus it's something I can use for a few other Excel projects. And ModestyBCatt - sorry, but I didn't even try your suggestion since Klarcks was so spot on.
posted by Slack-a-gogo at 4:46 AM on January 7, 2005
posted by Slack-a-gogo at 4:46 AM on January 7, 2005
"Sorry", that's not good enough, I demand you try it out!
No, glad you got your solution and I've just learnt something new and handy too. Winner all round.
posted by ModestyBCatt at 5:31 AM on January 7, 2005
No, glad you got your solution and I've just learnt something new and handy too. Winner all round.
posted by ModestyBCatt at 5:31 AM on January 7, 2005
I know you got your question answered, but for future reference, the guy who runs J-Walk Blog has written books on Excel and could probably answer any question you could care to ask.
posted by Doohickie at 8:03 AM on January 7, 2005
posted by Doohickie at 8:03 AM on January 7, 2005
This thread is closed to new comments.
Use the next blank column on the right of *all* the data and in the add the function RIGHT($cell,9) in there. Where $cell is the referance to the cell in your SKU column.
That *should* give you the right hand digits that you can sort on. I expect you can tidy this up by using the MID function instead, but RIGHT should do it.
Then copy that cell all the way down so it's being applied to all the rows.
Then sort.
Hope that makes sense.
posted by ModestyBCatt at 4:24 AM on January 7, 2005