Need to Sort String of Letters in Cell

R

rwmill9716

Active Member
Joined
May 20, 2006
Messages
495
Office Version
  1. 2013
Platform
  1. Windows
C column has cells filled with letters beginning in Cell C8. Each of these is combined with the letter combination in Cell D7 (e.g., C9&D$7) to form the combinations beginning in Cell D9 and following below in Col D. I need to sort those letters within each cell into Col E, then eliminate the letters that appear twice (Max possible) and place the remaining in Col F.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Design Generators</td><td style="text-align: center;border-top: 1px solid black;;">ABD</td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-left: 1px solid black;;">ABD</td><td style="text-align: center;background-color: #000000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-left: 1px solid black;;">ACE</td><td style="text-align: center;;">ABDACE</td><td style="text-align: center;;">AABCDE</td><td style="text-align: center;border-right: 1px solid black;;">BCDE</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;border-left: 1px solid black;;">BCF</td><td style="text-align: center;;">ABDBCF</td><td style="text-align: center;;">ABBCDF</td><td style="text-align: center;border-right: 1px solid black;;">ACDF</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;;">ABCG</td><td style="text-align: center;border-bottom: 1px solid black;;">ABDABCG</td><td style="text-align: center;border-bottom: 1px solid black;;">AABBCDG</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;;">CDG</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Thanks for your help.

Ric
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Let me simplify this request. How do I sort a string of letters (variable length) within a cell?

For example, I need 'ABCGAG' to be sorted into 'AABCGG'
 
Upvote 0
UDF?

Code: 
Function StrSort(sInp As String, _
                 Optional bAsc As Boolean = True, _
                 Optional bCaseSens As Boolean = False) As String
    ' shg 2008, 2011
    ' UDF or VBA
    ' Insertion-sorts sInp
    Dim i           As Long
    Dim j           As Long
    Dim s           As String
    Dim iComp       As vbCompareMethod
    Dim iSign       As Long
 
    StrSort = sInp
    iComp = IIf(bCaseSens, vbBinaryCompare, vbTextCompare)
    iSign = IIf(bAsc, 1, -1)
 
    For i = 2 To Len(sInp)
        s = Mid(StrSort, i, 1)
        For j = i - 1 To 1 Step -1
            If StrComp(Mid(StrSort, j, 1), s, iComp) <> iSign Then Exit For
            Mid(StrSort, j + 1) = Mid(StrSort, j, 1)
        Next j
        Mid(StrSort, j + 1) = s
    Next i
End Function

E.g., =StrSort(A1)
 
Upvote 0
Or to sort and eliminate letters that are duplicated,
Code: 
Function StrSort2(sInp As String) As String
    Dim i           As Long         ' index to string characters
    Dim aiNum(0 To 255) As Long     ' count of characters by code
    Dim iAsc        As Long         ' index to aiNum
    For i = 1 To Len(sInp)
        iAsc = Asc(Mid(sInp, i, 1))
        aiNum(iAsc) = aiNum(iAsc) + 1
    Next i
    For iAsc = 0 To 255
        If aiNum(iAsc) = 1 Then StrSort2 = StrSort2 & Chr$(iAsc)
    Next iAsc
End Function
 
Upvote 0
Thanks, SHG, this function works well to sort the string of letters.

Is it now possible to take this sorted string and eliminate letters that are
there twice? Note, only single and double letter combinations can occur.

For example, with your function, I can take 'A1: ABDBCF' to 'B1: ABBCDF' or 'A2: ABDABCG' to 'B2: AABBCDG' I now need to take these to 'ACDF' and 'CDG' in cells C1 and C2.

Thanks Again,

Ric
 
Upvote 0
If yer waitin' for me, yer goin' backwards ... :biggrin:
 
Upvote 0
SHG,

StrSort(A1) sorts the string into B1, but StrSort(B1) doesn't eliminate
double letters; it just resorts B1.

For Examples,

a1: ABDABEF B1: AABBDEF C1: ABEFABD

Instead, C1 should be 'DEF'
 
Last edited:
Upvote 0
Put a string of capital letters you want sorted and duplicates removed in cell A1.
Run this code and see if it's your sort of thing. Result in C1.
Code: 
Sub test()
Dim a As Variant
Dim i&, j&
Dim b(65 To 90) As Boolean
Dim k As String
a = Range("A1").Value
For j = 1 To Len(a): For i = 65 To 97
    If Chr(i) = Mid(a, j, 1) Then b(i) = True
Next i, j
For i = 65 To 90
    If b(i) Then k = k & Chr(i)
Next i
Range("C1") = k
End Sub
 
Upvote 0
This sub gets rid of the 2nd member but leaves the first so 'ABDABCEFG' in A1 puts
'ABCDEFG' in C1. I need C1 to equal 'CDEFG'. i.e., get rid of both As and both Bs. I'm also
going to have ~100 of these in Col A that I would like to convert to Col C.
 
Upvote 0
Are you sorting the letters only in order to be able to identify the duplicated letters? If so, then this function (UDF) will remove the duplicated letters without sorting the letters...

Code: 
Function RemoveDupes(ByVal S As String) As String
  Dim X As Long
  For X = 65 To 90
    If UBound(Split(S, Chr(X))) > 1 Then S = Replace(S, Chr(X), "")
  Next
  RemoveDupes = S
End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
580
mumps
mumps
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
E
  • Question Question
Adding a custom message.
Replies
1
Views
663
Micron
M
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Forum statistics

Threads
1,224,566
Messages
6,179,558
Members
452,928
Latest member
101blockchains

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top