Split text string to different columns

**liarliar** · 06-04-2008, 03:54 AM

I have a large amount of data and want to split the data into four coloumns.The slpit parameters are fixed.

First coloumn will have the time stamp - [*27/05/2008 13:08:23 SH*]
Second coloumn query type- Client Query
Third Colomn - Date (which is just after Query)
Forth coloumn- Rest of the text

**mudraker** · 06-04-2008, 08:24 AM

Try this macro
It assumes data in column A
Places data in columns B to E of same row

Sub SplitText()
   Dim sTxt As String
   Dim iPos(1) As Integer
   Dim l4Row As Long
   
   For l4Row = 1 To Cells(Rows.Count, "a").End(xlUp).Row Step 1
      sTxt = Cells(l4Row, "a").Value
      iPos(0) = InStr(1, sTxt, "]")
      Cells(l4Row, "b").Value = Left(sTxt, iPos(0))
      iPos(1) = InStr(iPos(0), sTxt, "Query")
      Cells(l4Row, "c").Value = Mid(sTxt, iPos(0) + 3, (iPos(1) - iPos(0)) + 2)
      Cells(l4Row, "d").Value = Mid(sTxt, iPos(1) + 6, 8)
      Cells(l4Row, "e").Value = Mid(sTxt, iPos(1) + 15)
   Next l4Row
End Sub

**Shijesh Kumar** · 06-04-2008, 09:13 AM

Hi,

Alternatively you can achieve the same by using formula as well


For first column:
=LEFT(A1,SEARCH("]",A1))

For second column:
=MID(A1,LEN(B1)+3,SEARCH("Query",A1,LEN(B1))-LEN(B1)+3)

for third column:
=MID(A1,LEN(B1)+LEN(C1)+3,8)

for forth column:
=RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1)-LEN(D1)-4)