Convert Text to Table?

[csutaria]

Does anyone know the syntax to convert text to table using 2 delimiters (~ to switch to the next column and "&" to switch to the next row)?

I'm planning to use it as part of a macro for MS Project 2003 but I figure it would be pretty much the same in excel.

Any help would be greatly appreciated.

 

[schielrn]

I'm not following exactly what you are asking here?

Do you have a lot of data in a cell separated by ~ and & and then depending on those to move the data to the next column or row?

 

[ZVI]

Hi,

Try these macros and choose the applicable one:

<font face=Courier New>
' Using of Excel .TextToColumns method
Sub TxtToExcelColumns(Txt As String, DestUpperLeftCell As Range)
  Dim arr
  arr = Split(Txt, "&")
  With DestUpperLeftCell.Cells(1, 1).Resize(UBound(arr) + 1)
    .Value = arr
    .TextToColumns Destination:=.Cells(1, 1), _
                   ConsecutiveDelimiter:=False, _
                   DataType:=xlDelimited, _
                   Other:=True, OtherChar:="~"
  End With
End Sub

Sub Test_TxtToExcelColumns()
  Dim Txt As String
  Txt = "Txt1.1~Txt1.2~Txt1.3~1~2~3&Txt2.1~Txt2.2~Txt2.3~4~5~6"
  ' If Txt is in [A1] cell then uncomment the line below
  'Txt = Range("A1").Value
  TxtToColumns Txt, Range("A2")
End Sub
</FONT>

<font face=Courier New>
' Using of Replace function
Function TxtToTabColumns(Txt As String)
  TxtToTabColumns = Replace(Replace(Txt, "&", vbLf), "~", vbTab)
End Function

Sub Test_TxtToTabColumns()
  Dim Txt As String
  Txt = "Txt1.1~Txt1.2~Txt1.3~1~2~3&Txt2.1~Txt2.2~Txt2.3~4~5~6"
  MsgBox TxtToTabColumns(Txt)
End Sub</FONT>

Regards,
Vladimir