Help with formula to "FIND" numbers within a cell that contains other numbers

[TheReaper]

Hello guys!
How is it going?

I'm looking for your precious help again with a formula to "Find" some characters. I hope you could guide me through...

Here is what I need to do:

- I have a database with cells that contains a series of "names" within each cell... I need to sort (alphabetically) them alphabetically. -Not to sort the cells, to sort the content within the cells... you know, concatenate them alphabetically. Here is an example of the text within a cell:

+++++++++++++++++++++++++++++++++++++++++++++++++++++
Sciences; Engineering & Applied Sciences; Arts, Architecture & Applied Arts; Social Sciences; Law, Politics & Government; General; Business & Economics; Journalism & Communications; Health Sciences; Languages & Literatures
+++++++++++++++++++++++++++++++++++++++++++++++++++++

I need to sort that content alphabetically. Each ";" is the separator between concepts.

==============================================
I've been trying to replace each name with numbers to make it easier (i.e. Sciences = 12), and I get a result like: 12,3,1,13,9,4,2,7,5,8
(Max value is 13)

That's when I got stuck!...
First I tried a formula to look for each number, however, formula stops after each "," (comma). FAILURE!

For example, in a column I placed a formula to look for the number "1", then, in another column to look for number "2", and so on. But if the number I'm looking for is before or after a comma, it returns me an error.

============================
Then, I tried to extract each number and later (somehow) concatenate them with the proper sort... I'm using a series of formulas to do that, but then I get errors after the first two-digit numbers. FAILURE!

Hope someone could help me! Thanks in advance guys you're amazing!

 

[VoG]

Assuming that your list is in column A try this macro

Sub alphabet()
Dim X As Variant, k As Long, LR As Long
Dim First As Long, Last As Long
Dim i As Long, j As Long, Temp As Variant
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
For k = 1 To LR
    With Range("A" & k)
        X = Split(.Value, ";")
            First = LBound(X)
            Last = UBound(X)
            For i = First To Last - 1
                For j = i + 1 To Last
                    If X(i) > X(j) Then
                        Temp = X(j)
                        X(j) = X(i)
                        X(i) = Temp
                    End If
                Next j
            Next i
        .Value = Trim(Join(X, ";"))
    End With
Next k
Application.ScreenUpdating = True
End Sub

 

[VoG]

P.S. This works with the names, no need to convert to numbers.

 

[TheReaper]

Thanks a lot Peter! It works perfect.

-Emilio