Sorting rows and columns using VBA

[vedrah]

Hi
Can anyone tell me how to do this :-

From :

Empty Locations
U-Sites:
U1H1, U3B2, U5C1
U7C1, U7E1
Q-Sites:
Q1D1, Q1D3

To :

Empty Location U1H1
Empty Location U3B2
Empty Location U5C1
Empty Location U7C1
Empty Location U7E1
Empty Location Q1D1
Empty Location Q1D3


Using VBA.

Thanks.

 

[Rob van Gelder]

Sub test()
Dim rngSource As Range, rngDest As Range
Dim rng As Range, arr() As String, i As Long

Set rngSource = Range("A1:A5")
Set rngDest = Range("B1")

For Each rng In rngSource
If Not Right(rng.Value, 1) = ":" Then
arr = Split(rng.Value, ",")
For i = LBound(arr) To UBound(arr)
rngDest.Value = "Empty Location " & Trim(arr(i))
Set rngDest = rngDest.Offset(1, 0)
Next
End If
Next
End Sub

 

[vedrah]

how do i put Empty Location and U1H1 etc in separate columns