Sort text in Cell

[alexoigres]

Hello,

I want to sort some values inside a cell

Example:

A2
19,48,35,21

What I want is

A2
19,21,35,48

I have a new file every week and I want to create a macro to do this.

Any ideas??
thank you

 

[Zack Barresse]

Hi there,

Try using something like this ...

Option Explicit

Function SortCell(rng As Range, Optional Delimiter As String) As String
    Dim arr() As String, arrTmp() As String, i As Long
    If rng.Text = "" Then Exit Function
    If Delimiter = "" Then Delimiter = ","
    If InStr(1, rng.Text, Delimiter) = 0 Then
        SortCell = rng.Text
        Exit Function
    End If
    arr = Array_Sort(Split(rng.Text, Delimiter))
    For i = LBound(arr) To UBound(arr)
        SortCell = SortCell & arr(i) & ","
    Next i
    SortCell = Left(SortCell, Len(SortCell) - 1)
End Function

Private Function Array_Sort(ByVal NotSortedArry As Variant) As Variant
'Originally coded by Masaru Kaji: http://puremis.net/excel/code/022.shtml
    Dim i As Long, j As Long, vElm As Variant
    For i = LBound(NotSortedArry) To UBound(NotSortedArry)
        For j = i + 1 To UBound(NotSortedArry)
            If NotSortedArry(i) > NotSortedArry(j) Then
                vElm = NotSortedArry(j)
                NotSortedArry(j) = NotSortedArry(i)
                NotSortedArry(i) = vElm
            End If
        Next
    Next
    Array_Sort = NotSortedArry
End Function

With your data in A2, in B2 (or desired) enter:

=SortCell(A2)

or

=SortCell(A2,",")


HTH

 

[alexoigres]

Sorry man.... I can't make it works........ I don't know what I'm doing wrong.

If you can send me an excel file with the macro running to compare mine that will help me a lot..

Thank you so much

alexoigres@hotmail.com

 

[erik.van.geit]

Sorry man.... I can't make it works........ I don't know what I'm doing wrong.

Hi,
"not working" doesn't tell much about what's going wrong
do you get results ?
what are the results ?

Your problem might be the numbers are sorted alphabetically instead of numeric order
entering
50,15,4,6,7,77,701
returns
15,4,50,6,7,701,77
instead of
4,6,7,15,50,77,701

if this is your problem change one line in the code
I added Val(.....) twice

If Val(NotSortedArry(i)) > Val(NotSortedArry(j)) Then

kind regards,
Erik

 

[erik.van.geit]

... when looking more deeply
(checking code to put in library) ...

these lines

    For i = LBound(arr) To UBound(arr) 
        SortCell = SortCell & arr(i) & "," 
    Next i 
    SortCell = Left(SortCell, Len(SortCell) - 1)

are "join"ing the array together

you can replace these lines by

SortCell = Join(arr, Delimiter)

 

[Zack Barresse]

Wow, all this time and I've never knew about the Join function.. Thanks Erik!! Learn something new everyday!

 

[erik.van.geit]

You're welcome

if I may ask this...

it's obvious the following lines are there to avoid problems in case there is only one item (no delimiter)

    If InStr(1, rng.Text, Delimiter) = 0 Then 
        SortCell = rng.Text 
        Exit Function 
    End If

Yet I cannot make the code bug when deleting those four lines
Can you tell: when are they needed ?
Can you show a bug when they are not there ?

best regards,
Erik

 

[alexoigres]

I'm so so sorry guys. I should have started saying I don't know a lot of Macros.... What I did was copy exactly the code that firefytr gave me, after that I opened a excel file and press ALT-F11 , then insert module and pasted the code.

Maybe your going to laught at me but like I said I'm new with Macros. When I tried to run a Macro there is nothing to run. There is no Macro name to run... so I created one.

I put at the begining of the Macro

Public Sub NEWMACRO()
and at the end of the code I put
End Sub

But it just give error, and actually my data starts in column U4,U5,U6.....etc


Can you help me with this? I tought it was going to be really easy : (
SG

 

[Joe Was]

Easy error to make. The code is not a Macro it is a Custom User Defined Function.

These are made by code and put into a Module, Like: Module1

You run the UDF [User Defined Function] just like any Excel Function!

In a Cell put:

=SortCell(D5)

and it works automatically.

 

[alexoigres]

Thank you for clarifing this. I tried the code and it worked perfect. Now the problem that I have is that everytime i want to do this i have to add a new column to put
=SortCell(A2,",")

I have already a macro in this file, and add a new column is extra work, because my columns are in text, and i have an other macro to do other load my information to my sistem so... do you think i can have a small macro that i just can add it to my other macro.

thank you for everything guys
SG