sort within each cell - vba udf required

asn1824

New Member
Joined
Jan 31, 2009
Messages
3
Let's say I have cells that each contain a number string, and the cells themselves are formatted as text:

323435344435055
055443543534323
435340553234435

I want to sort within each cell so the output data for all looks like (must be text format because of the potential 0's at the beginning of the string):

023333344445555 - output sample

Number of characters per cell could differ. Solution REQUIRED with VBA UDF

As always, thanks in advance.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Hello, you can do by formulae if you prefer... eg:

Code:
=REPT("0",LEN(A1)-LEN(SUBSTITUTE(A1,"0","")))&REPT("1",LEN(A1)-LEN(SUBSTITUTE(A1,"1","")))&REPT("2",LEN(A1)-LEN(SUBSTITUTE(A1,"2","")))&REPT("3",LEN(A1)-LEN(SUBSTITUTE(A1,"3","")))&REPT("4",LEN(A1)-LEN(SUBSTITUTE(A1,"4","")))
continued to 9
 

asn1824

New Member
Joined
Jan 31, 2009
Messages
3
Hi Ote,

Thanks for your prompt but it doesnt seems to useful for me as i would require vba function for the same.

I found this

Function OrderCell(sStr As String)

OrderCell = Join(Evaluate("transpose(if(row(1:" & Len(sStr) & "),small(--mid(""" & sStr & """,row(1:" & Len(sStr) & "),1),row(1:" & Len(sStr) & "))))"), "")
End Function

But the problem with this is that it works in Exel VBA but when I put it in Acess VBA the Evaluate function doesnt seem to be recognizied by Access VBA.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try
Code:
Function OrderCell(sStr As String)
Dim i As Long, ii As Long, a()
ReDim a(1 To Len(sStr))
For i = 1 To Len(sStr))
    a(i) = Mid$(sStr, i, 1)
Next
For i = 1 To UBound(a) - 1
    For ii = i + 1 To UBound(a)
        If a(i) > a(ii) Then
            temp = a(ii) : a(ii) = a(i) : a(i) = temp
        End If
    Next
Next
OrderCell = Join(a, "")
End Function
 

asn1824

New Member
Joined
Jan 31, 2009
Messages
3

ADVERTISEMENT

hi jindon, works fine thank you so much
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
If A1 holds the string "323435344435055", this CSE formula will return the number 023333344445555 , which can be formatted 000000000000000

=SUM(SMALL(MID(A1,ROW(Z1:Z15),1)+0,ROW(Z1:Z15))*10^(15-ROW(Z1:Z15)))

if you want a string
=TEXT(SUM(SMALL(MID(A1,ROW(Z1:Z15),1)+0,ROW(Z1:Z15))*10^(15-ROW(Z1:Z15))),"000000000000000")

Both of these need to be confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
Sorry, I missed the bit about variable string length.

=TEXT(SUM(SMALL(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0,ROW(INDIRECT("1:"&LEN(A1))))*10^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))))),G2)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,738
Messages
5,524,546
Members
409,583
Latest member
gkarthick

This Week's Hot Topics

Top