need a macro to replace and sort within a cell

[kimsa]

I have words in a cell separated by a semicolon and need a macro to replace the ";" and put the words in a alpha list within the cell

have this:
black;blue;green;red;purple

want this:

black
blue
green
purple
red

 

[Fluff]

Re: need a macro to repace and sort within a cell

How about

Sub SplitSort()
   Dim Lst As Object
   Dim Elm As Variant
   Dim cl As Range
   
   Set Lst = CreateObject("system.collections.arraylist")
   For Each cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      For Each Elm In Split(cl, ";")
         Lst.add Elm
      Next Elm
      Lst.Sort
      cl = Join(Lst.toarray, Chr(10))
      Lst.Clear
   Next cl
End Sub

 

[kimsa]

Re: need a macro to repace and sort within a cell

thank you this worked well how do I edit it because want it to run for three columns and to apply it to columns C2:C52, D2:D52, E2:E52. Also within a cell I will have 5-10 words and all will be in alpha order except the last word is not and not sure why?

How about

Sub SplitSort()
   Dim Lst As Object
   Dim Elm As Variant
   Dim cl As Range
   
   Set Lst = CreateObject("system.collections.arraylist")
   For Each cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      For Each Elm In Split(cl, ";")
         Lst.add Elm
      Next Elm
      Lst.Sort
      cl = Join(Lst.toarray, Chr(10))
      Lst.Clear
   Next cl
End Sub

 

[Fluff]

Re: need a macro to repace and sort within a cell

This will work on the range specified

Sub SplitSort()
   Dim Lst As Object
   Dim Elm As Variant
   Dim cl As Range
   
   Set Lst = CreateObject("system.collections.arraylist")
   For Each cl In Range("C2:E52")
      For Each Elm In Split(cl, ";")
         Lst.add Elm
      Next Elm
      Lst.Sort
      cl = Join(Lst.toarray, Chr(10))
      Lst.Clear
   Next cl
End Sub

Can you show some examples of the text that is not getting sorted correctly.

 

[kimsa]

Re: need a macro to repace and sort within a cell

this is some text that did not get sorted:

economic burden
epidemilogy
survival
quality of life

or:

disease
genetics
lung cancers
sclc
mutated genes

 

[Fluff]

Re: need a macro to repace and sort within a cell

This is what I get

Excel 2013/2016
	A	B	C
1	Before		After
2	economic burden;epidemilogy;survival;quality of life		economic burden epidemilogy quality of life survival
3	disease;genetics;lung cancers;sclc;mutated genes		disease genetics lung cancers mutated genes sclc
Sheet2

What are your "before" values?

 

[kimsa]

Re: need a macro to repace and sort within a cell

i figured the problem is that I have a space after the semicolon. Can I have a step first to remove the spaces after the semicolon? Can you please give me the code for that so my before looks like this:

economic burden; epidemilogy; survival; quality of life


sorry I am a newbie at these macros, I really appreciate your help, It has save me great time!

 

[Fluff]

Re: need a macro to repace and sort within a cell

Simply add a space after the ; on this line as shown

For Each Elm In Split(cl, "; ")

 

[kimsa]

Re: need a macro to repace and sort within a cell

I have words in a cell cell and would like to have the brackets and single quotes removed and put in alpha order. I have this in a single cell: ['red', 'blue', 'green', 'yellow']? I wnat this in a single cell:

blue
green
red
yellow

 

[Fluff]

Re: need a macro to repace and sort within a cell

As this is now a different question, please start a new thread.