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.


---
Message posted from http://www.ExcelForum.com/

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"vedrah " wrote in message
...
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.


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com