convert text to columns
 

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.

convert text to columns
 

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


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Cindi" wrote in message ...
|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.

convert text to columns
 

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.

"Niek Otten" wrote:

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


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Cindi" wrote in message ...
|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.

convert text to columns
 

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

Cindi wrote:

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.

--

Dave Peterson

convert text to columns
 

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
Insertsymbol 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" wrote:

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

Cindi wrote:

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.

--

Dave Peterson

convert text to columns
 

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.)



Cindi wrote:

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
Insertsymbol 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" wrote:

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

Cindi wrote:

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.

--

Dave Peterson


--

Dave Peterson

convert text to columns
 

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

"Dave Peterson" wrote:

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.)



Cindi wrote:

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
Insertsymbol 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" wrote:

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

Cindi wrote:

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.

--

Dave Peterson


--

Dave Peterson