Small square in the cells of CSV files - Text to column

[Hari Prasadh]

Hi,

I have got some data from my client (in CSV format) and one of the columns
has a small square at the end. Also, many of the cells in that column
contain extra lines in them (just like Alt+Enter).

I used Trim function on this column but neither the square nor the extra
lines are getting removed.

I tried Text to columns with fixed width and NO break line thinking that
this might remove the lines or extra lines but it didnt.

In Text to columns with Delimiter selected as TAB it worked (It seemed so).
Just wanted to know what this small square is. Is it equivalent to TAB?
And is the method of text to columns with delimiter being tab a SAFE way to
clean data of above mentioned type?

Thanks a lot,
Hari
India

 

[Guest]

Hi Hari

I had the same problem, Tom Ogilvy said use this line of code to remove it

Cells.Replace What:=Chr(3), Replacement:="", lookat:=xlPart

HTH

Regards Aussie Bob C.

 

[Tom Ogilvy]

That would assume that the character has an ascii code of 3.

You can examine what codes are there with

Sub Checkcode()
Dim sChr As String, sSTr As String
For i = 1 To Len(ActiveCell.Text)
sChr = Mid(ActiveCell.Text, i, 1)
sSTr = sSTr & Asc(sChr) & "-" & sChr & vbNewLine
Next
MsgBox sSTr
End Sub

Note that you probably won't see the small square, but you should see a
number without a printable character next to it. You would replace chr(3)
with chr(that number)

You would could also try the worksheet function CLEAN.

 

[Hari Prasadh]

Hi Robert,

Thnx for the help and especially the syntax.

Thanks a lot,
Hari
India

 

[Hari Prasadh]

Hi Tom,

Many times I CSV data from dont know what kind of software and it becomes an
issue to load it in to SPSS. With your generalised solution --You would
replace chr(3) with chr(that number) -- I can programmatically take care of
the cleaning in excel and then upload the data further.

Thnx a lot for CLEAN as well (It works nicely). Wasnt aware of any thing
other than TRIM till now.

Regards,
Hari
India