I have an Excel column of surnames. Is there any way of
sorting the letters within each surname into alphabetical
order, OTHER than by using left, mid and right functions
to strip out the individual letters and then perform a
sort on them. Thanks in advance. Bobf
Bob,
You need to strip them out as Sort has no substring facility in the key.
Other than that you need VBA.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"bobf" <[email protected]> wrote in message
news:[email protected]...
> I have an Excel column of surnames. Is there any way of
> sorting the letters within each surname into alphabetical
> order, OTHER than by using left, mid and right functions
> to strip out the individual letters and then perform a
> sort on them. Thanks in advance. Bobf
Hi!
Use Data > Text to columns. Set switch as fixed length and use length = 1
This will expand the word to one letter per column which is sortable using Data > Sort > Options > Sort left to right.
Alf
You've been told how to split the data, but my question for you is, WHY do you
want/need to do this?
On Mon, 21 Mar 2005 00:37:17 -0800, "bobf"
<[email protected]> wrote:
>I have an Excel column of surnames. Is there any way of
>sorting the letters within each surname into alphabetical
>order, OTHER than by using left, mid and right functions
>to strip out the individual letters and then perform a
>sort on them. Thanks in advance. Bobf
Myrna:
Gosh! I've never felt so bold I could ask that...
Alf
I use this to sort variations on a phrase into groups (similar to the free online OpenRefine's "Facet").
Sort letters.JPG
Assuming your names are in column A, with a header in A1, then:
- In cells B1 to AA1 type the lowercase alphabet (one letter per cell)
- In B2 type =REPT(B$1,LEN(LOWER($A2))-LEN(SUBSTITUTE(LOWER($A2),B$1,"")))
- Copy this formula across to AA2 and down the length of your name list
- In AB1 type "Sorted Names" (or whatever)
- In AB2 type =CONCATENATE(B2,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2,Z2,AA2)
- Copy this formula down the length of your name list
If you want to group the sorted names into groups:
- Sort the table by column AB
- In cell AC1 type "Groups"
- In cell AC2 type =IF(COUNTIF($AB$2:AB2,AB2)=1,MAX($AC$1:AC1)+1,AC1)
- Copy this formula down the length of your name list
Last edited by gothic chicken; 07-22-2017 at 01:00 AM.
There is always a better way.
protonLeah,
I've PM'd you about "Do not post a question ..."
gothic chicken correct me if I am wrong, but it looks to me like you are offering a suggestion, not asking a question there (albeit in a thread that is over 12 years old)?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
That's correct FDibbins. I'm not sure why protonLeah posted what they did.
I know the post is old, but I was looking for other answers to my version of "sorting letters in words into alphabetical order", and this post came up. I thought my method might help someone also - even 12 years from now!
Originally Posted by FDibbins
Thank you for posting - quite OK in the circumstances, and will be really helpful once you have attached a sample workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Re: sorting letters in words into alphabetical order
Hi Gothic Chicken
It's not quite 12 years (Only 5) but your code really helped me out of a sticky situation
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks