Sort by smallest to largest, multi lines, one cell

[mduntley]

I am wondering if its possible to have a VBA to convert a cell with multipule lines? I have a file that has 500+ cell that have multi-lines, so i am looking to have a costume vba where i can = to a cell. I have put an example what I want

[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD="width: 64"]Cell one[/TD]
[TD="width: 64"]1
6
8
7
11
23
456
4
12
13
465
251
631[/TD]
[/TR]
</tbody>[/TABLE]

We can see that in B1 the number is not sorted. I am wondering if there is a VBA formula to make it in order as the next example

[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD="width: 64"]Cell one[/TD]
[TD="width: 64"]1
4
6
7
8
11
12
13
23
251
456
465
631[/TD]
[/TR]
</tbody>[/TABLE]

 

[mduntley]

bump

 

[Peter_SSs]

You could try a user-defined function like this one. It assumes that the items on each line in the cell are numerical.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.

Function SortCell(s As String) As String
  Dim itm As Variant
  Dim AL As Object

  Set AL = CreateObject("System.Collections.ArrayList")
  For Each itm In Split(s, vbLf)
    AL.Add Val(itm)
  Next itm
  AL.Sort
  SortCell = Join(AL.ToArray(), vbLf)
End Function

Excel Workbook
	A	B
1	1687112345641213465251631	1467811121323251456465631
2	56	56
3
4	110168711112345641213465251631	467812132325145646563111011111
Sort

 

[mduntley]

You could try a user-defined function like this one. It assumes that the items on each line in the cell are numerical.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.

Function SortCell(s As String) As String
  Dim itm As Variant
  Dim AL As Object

  Set AL = CreateObject("System.Collections.ArrayList")
  For Each itm In Split(s, vbLf)
    AL.Add Val(itm)
  Next itm
  AL.Sort
  SortCell = Join(AL.ToArray(), vbLf)
End Function

Is there a way to make this code be available to live in another document and it will work in any new document?

 

[Peter_SSs]

Is there a way to make this code be available to live in another document and it will work in any new document?

Yes, move the function into your 'Personal.xlsb' workbook. However, you would then have to use the function like this
=PERSONAL.XLSB!SortCell(A1)

If you want to avoid having to prefix with PERSONAL.XLSB! each time, or add a reference to Personal.xlsb for each new workbook, you would need to create an Add-In. More information here and here or Google..

 

[mduntley]

My apologies to post on an old message, but I like this concept. How do I do this with text?

 

[mduntley]

Can anyone help me with this?