Hi,

Am Sun, 21 Jul 2013 16:10:28 +0200 schrieb Claus Busch:

I have below data in A1 cell (values separated by semi-colon)
A;C;B;M;U

ConvertToColumn() produces output as
A
C
B
M
U

and to write it back to a cell try:
Sub Test()
Dim varOut As Variant
Dim myRng As Range
Dim myStr As String

varOut = Split([A1], ";")
Set myRng = [B1].Resize(UBound(varOut) + 1, 1)
With myRng
.Value = WorksheetFunction.Transpose(varOut)
.Sort key1:=[B1], order1:=xlAscending, Header:=xlNo
End With
varOut = WorksheetFunction.Transpose(myRng)
myStr = Join(varOut, ";")
[C1] = myStr
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2