sort within each cell - vba udf required

[asn1824]

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.

 

[DonkeyOte]

Hello, you can do by formulae if you prefer... eg:

=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]

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]

try

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]

hi jindon, works fine thank you so much

 

[mikerickson]

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]

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)