Solved

Sorting the contents of a text string in Excel in Alphabetical order.

Posted on 2016-10-04
8
92 Views
Last Modified: 2016-10-13
Hello Gurus,

I have the need to sort a text string in alphabetical order.  (I would prefer a formula based solution if possible.)

Contents in cell A1:  A123,B456,C789
Desired Output:     ,,123456789ABC
As far as the order of the output, I don't really care.  Could be Numbers, Letters, Special Characters or any mix therein...

Can this be done?

Thank you.
0
Comment
Question by:Blistered Pawn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 41828216
See attached file.

I am sure this could be achieved without so many helper columns but this does what you want.

Columns B to O split the string into individual characters and converts each to its ASCII code equivalent. Columns Q to AD then use the SMALL function to put the codes from B to O into numerical order and convert them back to text characters. The concatenation in AF then combines them back together as one string.

No doubt you will have strings that are longer than 14 characters. Insert columns at column P and AE and copy across from previous columns. Then include the extra cells after AE in the formula in AF.

If you find out which is your longest string, you can insert sufficient columns to accommodate the longest as the formulas allow for shorter strings.

Can I ask why you want this???

Thanks
Rob H
Cell-sort.xlsx
0
 

Author Comment

by:Blistered Pawn
ID: 41828299
Hello Rob H.  Thank you for your response.

The need for this came from a problem that I posed in this post:  Index/Match Question

Your solution does accomplish the goal.  However, I have some text strings that are LEN=305+ characters in length.

The reason I want this is because I have a spreadsheet which contains Bill of Material data which quantities are suspect of being incorrect.

Sheet1 Columns (Model, ChildPN, Qty, DataPoint)       (No ParentPN column)
Sheet2 Columns (ParentPN, ChildPN, Qty, DataPoint)  (No Model column)

Trying to find a way to join the data to pull in ParentPN to Sheet1 and Model to Sheet2 has been challenging.  I have created helper columns for an INDEX/MATCH formula, but it has proven to be inaccurate.

While Sheet1 quantities are suspect, the DataPoint fields are out of order between Sheet1 and Sheet 2, but the data is the same.  Sorting these fields in alphabetical will provide a unique helper column to do my index/match formula.  There is likely a much better way, but I don't know how to get there quickly.

Thank you.  While your solution does the trick, can you think of a way to do this without all the needed helper fields?

I appreciate your help.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41829597
I have been looking at using a User Defined Function.

I can get the characters from the cell into a comma separated array but I can't find a way of then sorting that array. Still working on it though!!
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 250 total points
ID: 41829745
This is a type of bucket sort on the string's characters.
Public Function CollatedChars(ByVal parmString As String) As String
    Dim lngBuckets(0 To 255) As Long
    Dim lngLoop As Long
    Dim bChar As Byte
    For lngLoop = 1 To Len(parmString)
        bChar = Asc(Mid(parmString, lngLoop, 1))
        lngBuckets(bChar) = lngBuckets(bChar) + 1
    Next
    CollatedChars = vbNullString
    For lngLoop = LBound(lngBuckets) To UBound(lngBuckets)
        If lngBuckets(lngLoop) <> 0 Then
            CollatedChars = CollatedChars & String(lngBuckets(lngLoop), Chr(lngLoop))
        End If
    Next
End Function

Open in new window

0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 41829854
I reached out to other EE members for help:

https://www.experts-exchange.com/questions/28974440/Storing-values-to-an-array.html

With their help we came up with this combination of UDF and Macro:
Function SortChar(c As Range)
Dim Str As String
Dim MyString As Variant

L = Len(c.Value)
T = c.Value

For P = 1 To L
    Sep = ","
    If P = L Then Sep = ""
    NextVal = Mid(T, P, 1) & Sep
    Str = Str & NextVal
Next P

MyString = Split(Str, ",")
QuickSort MyString, LBound(MyString), UBound(MyString)
Ch = MyString(0)
SortChar = Join(MyString, "")
End Function

Sub QuickSort(arr, Lo As Long, Hi As Long)
  Dim varPivot As Variant
  Dim varTmp As Variant
  Dim tmpLow As Long
  Dim tmpHi As Long
  tmpLow = Lo
  tmpHi = Hi
  varPivot = arr((Lo + Hi) \ 2)
  Do While tmpLow <= tmpHi
    Do While arr(tmpLow) < varPivot And tmpLow < Hi
      tmpLow = tmpLow + 1
    Loop
    Do While varPivot < arr(tmpHi) And tmpHi > Lo
      tmpHi = tmpHi - 1
    Loop
    If tmpLow <= tmpHi Then
      varTmp = arr(tmpLow)
      arr(tmpLow) = arr(tmpHi)
      arr(tmpHi) = varTmp
      tmpLow = tmpLow + 1
      tmpHi = tmpHi - 1
    End If
  Loop
  If Lo < tmpHi Then QuickSort arr, Lo, tmpHi
  If tmpLow < Hi Then QuickSort arr, tmpLow, Hi
End Sub

Open in new window


Credit to Rgonzo1971 for the QuickSort sub and assistance from Saquib and Neeraj for correcting the array implications of the SortChar function.

To use either my or Aikimark's function solution, use the following in a cell:

=SortChar(CellReference)
=CollatedChars(CellReference)

where CellReference contains the string of text to be sorted.

A comment from RGonzo regarding Aikimark's code is that it will only work on Ascii code and not Unicode.

Thanks
Rob H
0
 
LVL 46

Expert Comment

by:aikimark
ID: 41829902
If the string has unicode characters, then the buckets array would increase to 64K.  I would probably use the StrConv() function instead of the Asc().  Alternatively, I could use the AscW() and ChrW() functions.
0
 
LVL 46

Accepted Solution

by:
aikimark earned 250 total points
ID: 41830187
Here's a unicode-friendly version of the function:
Public Function CollatedCharsUnicode(ByVal parmString As String) As String
    Dim lngBuckets(0 To 65535) As Long
    Dim lngLoop As Long
    Dim bChar As Long
    
    For lngLoop = 1 To Len(parmString)
        bChar = AscW(Mid(parmString, lngLoop, 1))
        lngBuckets(bChar) = lngBuckets(bChar) + 1
    Next
    CollatedCharsUnicode = vbNullString
    For lngLoop = LBound(lngBuckets) To UBound(lngBuckets)
        If lngBuckets(lngLoop) <> 0 Then
            CollatedCharsUnicode = CollatedCharsUnicode & String(lngBuckets(lngLoop), ChrW(lngLoop))
        End If
    Next
End Function

Open in new window

0
 

Author Closing Comment

by:Blistered Pawn
ID: 41841890
Thanks to everyone for your assistance.
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question