Sort number characters within a cell

[Captain Smith]

Let's say I have cells that each contain a number string, and the cells themselves are formatted as text:

358193
231454
12023
etc

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

Number of characters per cell could differ. Solution either with formula or VBA would be fine.

As always, thanks in advance.

 

[pgc01]

Hi

You can use this UDF:

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

Use it as a formula. In B1:
=OrderCell(A1)

Hope this helps
PGC

Edit: Simplified the expression

Book1
	A	B	C	D
1	358193	133589
2	231454	123445
3	12023	01223
4	789021334789980234123	001122233334477888999
5	9876543210	0123456789
6	98765432109876543210	00112233445566778899
7	987654321098765432109876543210	000111222333444555666777888999
8
Sheet2

 

[barry houdini]

Not very elegant but a formula solution which should work for strings containing any digits, including zeroes, but not more than 15 non-zero digits amongst them

=REPT(0,LEN(A1)-LEN(SUBSTITUTE(A1,0,"")))&SUM(10^(ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,0,""))))-1)*LARGE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0,ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,0,""))))))

confirmed with CTRL+SHIFT+ENTER

...or a formula using functions from Morefunc add-in which will work with any number of digits......

=MCONCAT(SMALL(MID(A1,INTVECTOR(LEN(A1),1),1)+0,INTVECTOR(LEN(A1),1)))

also confirmed with CTRL+SHIFT+ENTER

 

[Captain Smith]

Holy Cow - both solutions work great! Barry, I am especially impressed that you could do this with a formula. Elegance takes second place to a formula actually returning what it is supposed to!

Thanks very much both of you.

 

[Didaskalos]

By the way, the MoreFunc sort formula will return a greatest to least sort by changing SMALL to LARGE. This is not to say I understand entirely how the formula works (I don't). I just made an intuitive guess and it was correct. Even a blind hog finds an acorn once in a while.