Separation based on semi-colon

justinferns · February 27th, 2012, 10:00 AM

Hi,

I need a code that separates a string based on the semi-colon.

Eg: John;James;Sam;Tyler;Warden - (in a single cell)

O/P : John
James
Sam
Tyler
Warden

The O/p should be in different cells one below the other.

can anyone help me on this?

Thanks

Shasur · February 27th, 2012, 01:44 PM

Here is a hint

Code:

Sub ConvertText2Range()
Dim sText As String, arText
sText = Range("c16").Value
arText = Split(sText, ";")
Range("D16:D" & CStr(16 + UBound(arText))).Value = WorksheetFunction.Transpose(arText)
End Sub

Cheers
Shasur