Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy from a Cell to a text box. | Excel Worksheet Functions | |||
convert numbers to text | Excel Discussion (Misc queries) | |||
Macro to convert text to date | Excel Worksheet Functions | |||
Text to Columns / Importing Data | Excel Discussion (Misc queries) | |||
How do I convert text to columns when there is a carriage return? | Excel Worksheet Functions |