convert text to columns

[Guest]

I downloaded data as CSV, but the addresses came over to excel with "little
squares" at the end of each line, not a comma, so ended up in one cell. I
have tried to split using the convert text to column wizard but no luck. Is
there a way to key that "square" in the "Other" option of the deliminiator
choices? Is there a better way to split the addresses? The addresses are
not fixed length so can not use that option either. Using excel 2003. Thank
you.

 

[Niek Otten]

Copy that one "square" character and paste it into the Delimiter>Other box in the Text to Columns dialog


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I downloaded data as CSV, but the addresses came over to excel with "little
| squares" at the end of each line, not a comma, so ended up in one cell. I
| have tried to split using the convert text to column wizard but no luck. Is
| there a way to key that "square" in the "Other" option of the deliminiator
| choices? Is there a better way to split the addresses? The addresses are
| not fixed length so can not use that option either. Using excel 2003. Thank
| you.

 

[Guest]

I tried that. I can't right-click to paste, but tried control-v to paste it
in the box. Didn't work. I can't even copy it from one cell to another in
the worksheet. Any other ideas? Thank you.

 

[Dave Peterson]

Saved from a previous post...

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

Since you do see a box, then you can either fix it via a helper cell or a macro:

=substitute(a1,char(13),"")
or
=substitute(a1,char(13)," ")

Replace 13 with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in CellView?

myGoodChars = Array(" ","") '<--what's the new character, "" for nothing?

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Thank you for the response. Unfortunately, the computer I am on has no way
to unzip the cellview file. Using your approach, I tried to use
Insert>symbol tool to find the square symbol, the closest I found as 25A1 but
it returned an error in the first formula you suggested. Several other
character numbers just returned the entire address in a different font with
the same little square still there. I appreciate the effort though.

 

[Dave Peterson]

If you can isolate the character into its own cell, you could use:
=code(a1)

If you can't isolate it, maybe you could use:
=code(mid(a1,12,1))
(where that character is the 12th character in A1.)

 

[Guest]

WOW!!!! You are an angel! It took a little trial and error to isolate the
correct character using the second formula but it worked like a charm! FYI
the code for that darn little square is "13". Beaucoup beaucoup thanks