Funky Character in CVS - Text To Columns

[bubbapost]

Hello,

I have a CSV file that a specific system spits out with thousands of rows of data and it's all seperated by a funky character, a square with a question mark in it. I can't figure out how to type that character in the "Other" portion as a delimiter.

I have attached a sample spreadsheet as an example.

I have also tried VBA, but I still need to know how to reference the character. Here is my code below:

Sub DelimTextToColumn()
Cells.Replace What:="", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Any help would be much appreciated!

[GTO]

I see only the square box. At least in your .xlsx file, they appear to be tab characters.

[bubbapost]

I tried using "Tab" as the delimiter, but it didn't work.

[Kenneth Hobs]

Use vbTab.
Sub DelimTextToColumn()
Cells.Replace What:=vbTab, Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

[Paul_Hossler]

You can parse the data directly or use vbTab in your macro


Sub ParseData()

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True
End Sub
Sub DelimTextToColumn()

Selection.Replace What:=vbTab, Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub



Paul

[bubbapost]

Thanks Paul, that works great!!!!

[bubbapost]

Solved