# sort within each cell - vba udf required

#### asn1824

##### New Member
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

### 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
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
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
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

hi jindon, works fine thank you so much

#### mikerickson

##### MrExcel MVP
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
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)

Replies
6
Views
69
Replies
2
Views
79
Replies
5
Views
59
Replies
2
Views
36
Replies
3
Views
63