Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / February 2008

Tip: Looking for answers? Try searching our database.

alphabetize within a cell

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
hiflyinskiier@gmail.com - 14 Feb 2008 20:31 GMT
if i have a list of letters in a cell, is it possible to alphabetize
them?

example: ADCB --> ABCD
Ron Rosenfeld - 14 Feb 2008 20:41 GMT
>if i have a list of letters in a cell, is it possible to alphabetize
>them?
>
>example: ADCB --> ABCD

If your cells are contiguous and in a single column, here is a method that will
sort each cell and write the result into the cell in the adjacent column.  It
can be easily modified for different situations.

To enter this Sub, <alt><F11> opens the VB Editor.
Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this, select some cell in the column of cells to be sorted. Then
<alt><F8> opens the macro dialog box.  Select the macro and run.

Once satisfied that it is doing what you want, you can remove the "offset"
function from the line that writes the result, and overwrite the original, if
you want.

=======================================
Sub SortCell()
Dim Source As Range, Target As Range
Dim c As Range
Dim i As Long
Dim Temp()

Set Target = Selection.CurrentRegion.Offset(0, 1)
Set Target = Target.Resize(, 1)
Target.Clear
Set Source = Selection.CurrentRegion

For Each c In Source
   ReDim Temp(0 To Len(c.Text) - 1)
       For i = 0 To UBound(Temp)
           Temp(i) = Mid(c.Text, i + 1, 1)
       Next i
   SingleBubbleSort Temp
   c.Offset(0, 1).Value = Join(Temp, "")
Next c
End Sub

Function SingleBubbleSort(TempArray As Variant)
'copied directly from support.microsoft.com
   Dim Temp As Variant
   Dim i As Integer
   Dim NoExchanges As Integer

   ' Loop until no more "exchanges" are made.
   Do
       NoExchanges = True

       ' Loop through each element in the array.
       For i = 0 To UBound(TempArray) - 1

           ' If the element is greater than the element
           ' following it, exchange the two elements.
           If TempArray(i) > TempArray(i + 1) Then
               NoExchanges = False
               Temp = TempArray(i)
               TempArray(i) = TempArray(i + 1)
               TempArray(i + 1) = Temp
           End If
       Next i
   Loop While Not (NoExchanges)

End Function
================================
--ron
hiflyinskiier@gmail.com - 14 Feb 2008 21:04 GMT
> >if i have a list of letters in a cell, is it possible to alphabetize
> >them?
[quoted text clipped - 65 lines]
> ================================
> --ron

two things on that
1) is there a way to get it to go into the same cell (instead of the
one next to it)
2) is there a way to get it to alphabetize uppercase/lowercase letters
as one. (example: ABaC --> AaBC), where the current program does ABaC
--> ABCa
hiflyinskiier@gmail.com - 14 Feb 2008 21:08 GMT
On Feb 14, 1:04 pm, hiflyinski...@gmail.com wrote:

> > >if i have a list of letters in a cell, is it possible to alphabetize
> > >them?
[quoted text clipped - 72 lines]
> as one. (example: ABaC --> AaBC), where the current program does ABaC
> --> ABCa

also, to clarify:
if it is not possible to get it to go into the same cell, if one
formula could process an entire worksheet and put the results into a
new worksheet
Ron Rosenfeld - 14 Feb 2008 22:52 GMT
>also, to clarify:
>if it is not possible to get it to go into the same cell, if one
>formula could process an entire worksheet and put the results into a
>new worksheet

If you read ALL of my response, you will see the answer to this question of
yours.
--ron
Ron Rosenfeld - 14 Feb 2008 22:55 GMT
>2) is there a way to get it to alphabetize uppercase/lowercase letters
>as one. (example: ABaC --> AaBC), where the current program does ABaC
>--> ABCa

To make the routine case insensitive, change this line:

If TempArray(i) > TempArray(i + 1) Then

to this:

If UCase(TempArray(i)) > UCase(TempArray(i + 1)) Then
--ron
kounoike - 15 Feb 2008 02:07 GMT
>>2) is there a way to get it to alphabetize uppercase/lowercase letters
>>as one. (example: ABaC --> AaBC), where the current program does ABaC
[quoted text clipped - 8 lines]
> If UCase(TempArray(i)) > UCase(TempArray(i + 1)) Then
> --ron

I might be wrong, but I don't think only this change will satisfy the
requirement.

keiji
Ron Rosenfeld - 15 Feb 2008 02:15 GMT
>>>2) is there a way to get it to alphabetize uppercase/lowercase letters
>>>as one. (example: ABaC --> AaBC), where the current program does ABaC
[quoted text clipped - 11 lines]
>I might be wrong, but I don't think only this change will satisfy the
>requirement.

Since I am unable to read your mind, perhaps you could share your reasoning for
making that comment.
--ron
kounoike - 15 Feb 2008 04:03 GMT
> On Fri, 15 Feb 2008 11:07:52 +0900, "kounoike"
> <kounoike@nowherembh.nifty.com>
> wrote:

--snip

>>> To make the routine case insensitive, change this line:
>>>
[quoted text clipped - 12 lines]
> making that comment.
> --ron

sorry for unclear comment. if upper case necessarily come before lower case
is the requirement, i wonder this change could gurantee it.
Ron Rosenfeld - 15 Feb 2008 12:21 GMT
>sorry for unclear comment. if upper case necessarily come before lower case
>is the requirement, i wonder this change could gurantee it.

No, it would not.  But it was not designed to do that.  Rather, it was designed
to "alphabetize uppercase/lowercase letters as one".

To me, that statement of the requirement means to treat them as being
identical, hence, no ordering between different cases of the same letter.

If the OP wants to have a custom sort order, rather than merely a
case-insensitive sort order, that can be done without too much difficulty.  But
then I'd like to confirm, for example, that *only* letters [A-Za-z] are in the
cell, as he stated in his first post.  Or, if not, how he would want any
additional characters sorted.
--ron
kounoike - 15 Feb 2008 12:50 GMT
> On Fri, 15 Feb 2008 13:03:37 +0900, "kounoike"
> <kounoike@nowherembh.nifty.com>
[quoted text clipped - 19 lines]
> additional characters sorted.
> --ron

Thank you for your comment and i see your point. the requrirement might be
my one-sided understanding, but i would like to leave it to the OP.

keiji
Ron Rosenfeld - 15 Feb 2008 13:27 GMT
>Thank you for your comment and i see your point. the requrirement might be
>my one-sided understanding, but i would like to leave it to the OP.
>
>keiji

I would also.  But what he posted did not seem to me to specify anything other
than treating case-differentiated letters as the same.
--ron
Ron Rosenfeld - 15 Feb 2008 12:41 GMT
>> On Fri, 15 Feb 2008 11:07:52 +0900, "kounoike"
>> <kounoike@nowherembh.nifty.com>
[quoted text clipped - 21 lines]
>sorry for unclear comment. if upper case necessarily come before lower case
>is the requirement, i wonder this change could gurantee it.

Actually, to do the case insensitive sort, one only needs to add

Option Compare Text to the top of the module.
--ron
Ron Rosenfeld - 15 Feb 2008 12:42 GMT
>>2) is there a way to get it to alphabetize uppercase/lowercase letters
>>as one. (example: ABaC --> AaBC), where the current program does ABaC
[quoted text clipped - 8 lines]
>If UCase(TempArray(i)) > UCase(TempArray(i + 1)) Then
>--ron

I should alter that recommendation.

To do a case insensitive sort, do NOT make the change I stated above.

Rather, at the very top of the module enter

Option Compare Text

So the beginning should now read:

=====================
Option Explicit
Option Compare Text
Sub SortCell()
...
====================
--ron
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.