Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to col with embedded mystery box
Hi,
Im trying to use the text to col function in excel and have hit a snag. My source data looks like this: "DEPT OF SPECIAL SERVICES –¡ 760 COOPER ST.–¡ AGAWAM MA 01001" I think these little boxes are CR returns because when I paste it as is in word it appears line under line. When I do the text to columns feature and I choose OTHER as the seperator I can't insert this little box. Even when I do a find replace it doesn't see it. I want it so each cell a, b, c has the name, street address, city and state etc. I even tried doing a find replace to change the box to something else. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to col with embedded mystery box
Try this to replace the ALT ENTER with a semicolon
Sub FindAltEnter() Dim rng As Excel.Range, rng1 As Excel.Range Set rng = Cells.Find(What:=Chr(10), _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then sAddr = rng.Address Do If rng1 Is Nothing Then Set rng1 = rng Else Set rng1 = Union(rng1, rng) End If Set rng = Cells.FindNext(rng) Loop Until rng.Address = sAddr End If If Not rng1 Is Nothing Then rng1.Select rng1.Interior.ColorIndex = 6 Else MsgBox "None found" End If End Sub -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Craig860" wrote: Hi, Im trying to use the text to col function in excel and have hit a snag. My source data looks like this: "DEPT OF SPECIAL SERVICES –¡ 760 COOPER ST.–¡ AGAWAM MA 01001" I think these little boxes are CR returns because when I paste it as is in word it appears line under line. When I do the text to columns feature and I choose OTHER as the seperator I can't insert this little box. Even when I do a find replace it doesn't see it. I want it so each cell a, b, c has the name, street address, city and state etc. I even tried doing a find replace to change the box to something else. Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to col with embedded mystery box
didn't work. "None Found"
"Barb Reinhardt" wrote: Try this to replace the ALT ENTER with a semicolon Sub FindAltEnter() Dim rng As Excel.Range, rng1 As Excel.Range Set rng = Cells.Find(What:=Chr(10), _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then sAddr = rng.Address Do If rng1 Is Nothing Then Set rng1 = rng Else Set rng1 = Union(rng1, rng) End If Set rng = Cells.FindNext(rng) Loop Until rng.Address = sAddr End If If Not rng1 Is Nothing Then rng1.Select rng1.Interior.ColorIndex = 6 Else MsgBox "None found" End If End Sub -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Craig860" wrote: Hi, Im trying to use the text to col function in excel and have hit a snag. My source data looks like this: "DEPT OF SPECIAL SERVICES –¡ 760 COOPER ST.–¡ AGAWAM MA 01001" I think these little boxes are CR returns because when I paste it as is in word it appears line under line. When I do the text to columns feature and I choose OTHER as the seperator I can't insert this little box. Even when I do a find replace it doesn't see it. I want it so each cell a, b, c has the name, street address, city and state etc. I even tried doing a find replace to change the box to something else. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to col with embedded mystery box
Chip Pearson has a very nice addin that will help determine what that
character(s) is: http://www.cpearson.com/excel/CellView.aspx Depending on what that character is, you may be able to use alt-#### (from the number keypad) to enter the character into the Other box in the text to columns wizard dialog. In fact, you may be able to select the character (in the formula bar), and copy it. Then use ctrl-v to paste into that text to columns Other box. If that fails, you could use a different technique to change those funny character to a different (but type-able) character (like the broken vertical bar |). I saved this from a previous post. You may find it useful: You may be able to use Edit|Replace to change the character--Some characters can be entered by holding the alt-key and typing the hex number on the numeric keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've never been able to get alt-0013 to work for carriage returns. Another alternative is to fix it via a formula: =substitute(a1,char(##),"|") Replace ## 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(##)) '<--What showed up in CellView? myGoodChars = Array("|") 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: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Craig860 wrote: Hi, Im trying to use the text to col function in excel and have hit a snag. My source data looks like this: "DEPT OF SPECIAL SERVICES –¡ 760 COOPER ST.–¡ AGAWAM MA 01001" I think these little boxes are CR returns because when I paste it as is in word it appears line under line. When I do the text to columns feature and I choose OTHER as the seperator I can't insert this little box. Even when I do a find replace it doesn't see it. I want it so each cell a, b, c has the name, street address, city and state etc. I even tried doing a find replace to change the box to something else. Any ideas? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to col with embedded mystery box
Actually this post worked well I found on another message board. Thanks
anyway. It can be found at: http://www.ozgrid.com/forum/showthread.php?t=20451 Here is a VBA alternative. Select the column that needs the replacement applied to. Now go to the VBE (ALT+F11) immediate window (CTRL+G) and enter the following, Selection.Replace chr(13)," " which will replace carriage_returns with a space. Cheers Andy "Dave Peterson" wrote: Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.aspx Depending on what that character is, you may be able to use alt-#### (from the number keypad) to enter the character into the Other box in the text to columns wizard dialog. In fact, you may be able to select the character (in the formula bar), and copy it. Then use ctrl-v to paste into that text to columns Other box. If that fails, you could use a different technique to change those funny character to a different (but type-able) character (like the broken vertical bar |). I saved this from a previous post. You may find it useful: You may be able to use Edit|Replace to change the character--Some characters can be entered by holding the alt-key and typing the hex number on the numeric keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've never been able to get alt-0013 to work for carriage returns. Another alternative is to fix it via a formula: =substitute(a1,char(##),"|") Replace ## 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(##)) '<--What showed up in CellView? myGoodChars = Array("|") 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: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Craig860 wrote: Hi, Im trying to use the text to col function in excel and have hit a snag. My source data looks like this: "DEPT OF SPECIAL SERVICES –¡ 760 COOPER ST.–¡ AGAWAM MA 01001" I think these little boxes are CR returns because when I paste it as is in word it appears line under line. When I do the text to columns feature and I choose OTHER as the seperator I can't insert this little box. Even when I do a find replace it doesn't see it. I want it so each cell a, b, c has the name, street address, city and state etc. I even tried doing a find replace to change the box to something else. Any ideas? -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
text to col with embedded mystery box
Hi,
Could be TAB delimited. When you pick Delimited and click next turn on all the options and see if the preview displays something you want, if it does you don't need to resort to other approaches. -- Thanks, Shane Devenshire "Craig860" wrote: Hi, Im trying to use the text to col function in excel and have hit a snag. My source data looks like this: "DEPT OF SPECIAL SERVICES –¡ 760 COOPER ST.–¡ AGAWAM MA 01001" I think these little boxes are CR returns because when I paste it as is in word it appears line under line. When I do the text to columns feature and I choose OTHER as the seperator I can't insert this little box. Even when I do a find replace it doesn't see it. I want it so each cell a, b, c has the name, street address, city and state etc. I even tried doing a find replace to change the box to something else. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mystery text box I can't edit | Excel Discussion (Misc queries) | |||
Putting a text box on top of an embedded object | Excel Discussion (Misc queries) | |||
autofill with number embedded in text | Excel Discussion (Misc queries) | |||
Embedded Text Box | Excel Discussion (Misc queries) | |||
Hyperlinks embedded in text | Links and Linking in Excel |