Resorting numbers in a single cell

[ssianh]

Hi, it's me again.
I have this problem which i have no idea how to solve.
I have a tab that has numbers in all the cells

eg
6712
48923
8735
6680
4459980

I would like to sort them from the smallest to the biggest number like
6712 - 1267
48923 - 23489
8735 - 3578
6680 - 0668
4459980 - 0445899

Is there a way to go about doing this ?
Thanks

 

[JazzSP8]

Hi ssianh

There's a UDF that will do that for you on this link...

http://www.pcreview.co.uk/forums/arrange-alphabet-and-numbers-cell-t3880221.html

HTH

 

[ssianh]

Hi ssianh

There's a UDF that will do that for you on this link...

http://www.pcreview.co.uk/forums/arrange-alphabet-and-numbers-cell-t3880221.html

HTH

Thank you so much.
But i have no idea as to where to paste and how to execute those codes.

 

[Sektor]

This is UDF. Create standard module (Insert -> Module) and use it like Excel's built-in function: =SortNumbers(A1)

[COLOR="Blue"]Function[/COLOR] SortNumbers(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Double[/COLOR]

    [COLOR="Blue"]Dim[/COLOR] mc [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Object[/COLOR], s [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], j [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
   
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Global = [COLOR="Blue"]True[/COLOR]
        .Pattern = "\d"
        [COLOR="Blue"]Set[/COLOR] mc = .Execute(Str)
        [COLOR="Blue"]For[/COLOR] j = 9 [COLOR="Blue"]To[/COLOR] 0 [COLOR="Blue"]Step[/COLOR] -1
            [COLOR="Blue"]For[/COLOR] i = 0 [COLOR="Blue"]To[/COLOR] mc.Count - 1
                [COLOR="Blue"]If[/COLOR] mc(i) = j [COLOR="Blue"]Then[/COLOR]
                    s = s & j
                [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
            [COLOR="Blue"]Next[/COLOR]
        [COLOR="Blue"]Next[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
    SortNumbers = [COLOR="Blue"]CDbl[/COLOR](s)
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[JazzSP8]

Hi ssianh

No problem - What you need to do is access the Visual Basic Editor (VBE) by pressing ALT & F11

Once you see the new screen you'll notice your workbook located in a window on the left hand side of the screen, if your workbook was called 'Book 1' you'd see VBAProject(Book1) with the worksheets listed below.

You need to select that workbook and use the right mouse button to bring up a new menu, then choose 'Insert > Module'

A module will then be added to your project, double click it to activate it. You can then copy and paste the SortCell UDF from the link I provided into the space on the right hand side.

Close the VBE and return to your worksheet; you'll now have a new formula you can use (UDF = User Defined Function) so if your data is in cell A1 you'd use;

=SORTCELL(A1, TRUE)

Hope that makes sense?

 

[Trevor G]

You need to do 2 steps here with the suggested User Defined Function (UDF).

First take a copy of the test workbook, next Open the copy then use Alt + F11 to go into the VBA screen, next select Insert and Module. Then copy the code in the link into the module.

Next switch to your workbook screen (again Alt + F11 should do this), then select the cell to the right of your first cell with contents.

Next select the Formula Guide (F* on the formula bar), then select the drop down and select User Defined Function you will find the Function in there, then simply select then click OK this will give you a list, but not the way you want it.

You would then need another function to reverse the contents and also state it is text otherwise you loose the Zero.

Copy this code into the Module and then go back to the worksheet and to the right of the first cell (next blank), then use the Function guide to select this new UDF and then make sure you select the Text option you will then get the result you need.

See samples below:

Excel Workbook
	A	B	C
1	6712	7621	1267
2	48923	98432	23489
3	8735	8753	3578
4	6680	8660	0668
5	4459980	9985440	0445899
Sheet2

First Function

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> SortCell(myR <SPAN style="color:#00007F">As</SPAN> Range, OrdAsc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> myArr() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> myTemp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> j As <SPAN style="color:#00007F">Integer</SPAN><br><br><SPAN style="color:#007F00">'Split the string into characters</SPAN><br><SPAN style="color:#00007F">ReDim</SPAN> myArr(1 <SPAN style="color:#00007F">To</SPAN> Len(myR.Value))<br><SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Len(myR.Value)<br>myArr(i) = Mid(myR.Value, i, 1)<br><SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#007F00">'Do the sort</SPAN><br><SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">LBound</SPAN>(myArr) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(myArr) - 1<br><SPAN style="color:#00007F">For</SPAN> j = i + 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(myArr)<br><SPAN style="color:#00007F">If</SPAN> Ascending <SPAN style="color:#00007F">Then</SPAN><br><SPAN style="color:#00007F">If</SPAN> myArr(i) > myArr(j) <SPAN style="color:#00007F">Then</SPAN><br>myTemp = myArr(j)<br>myArr(j) = myArr(i)<br>myArr(i) = myTemp<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Else</SPAN><br><SPAN style="color:#00007F">If</SPAN> myArr(i) < myArr(j) <SPAN style="color:#00007F">Then</SPAN><br>myTemp = myArr(j)<br>myArr(j) = myArr(i)<br>myArr(i) = myTemp<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> j<br><SPAN style="color:#00007F">Next</SPAN> i<br><br><SPAN style="color:#007F00">'Return the sorted string</SPAN><br><br>SortCell = Join(myArr, "")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

Second Function

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> ReverseCell(Rcell <SPAN style="color:#00007F">As</SPAN> Range, <SPAN style="color:#00007F">Optional</SPAN> IsText <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> StrNewNum <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> strOld <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    strOld = Trim(Rcell)<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Len(strOld)<br>        StrNewNum = Mid(strOld, i, 1) & StrNewNum<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">If</SPAN> IsText = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        ReverseCell = <SPAN style="color:#00007F">CLng</SPAN>(StrNewNum)<br>    <SPAN style="color:#00007F">Else</SPAN><br>        ReverseCell = StrNewNum<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

 

[ssianh]

Thank you all for explaining to me
shall give it a try when i'm home later