Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cartridge Returns - How to remove
Greetings,
I have spent quite a lot of time researching and trying out ways to find and remove cartridge return "indicators" (small boxes) from the end of email addresses in a spreadsheet to no avail. I have tried the Find/Replace with ALT+0010/0013 solution, but it didn't help. Any ideas would be very appreciated! -B |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cartridge Returns - How to remove
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.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. 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.) Outlook, eh? wrote: Greetings, I have spent quite a lot of time researching and trying out ways to find and remove cartridge return "indicators" (small boxes) from the end of email addresses in a spreadsheet to no avail. I have tried the Find/Replace with ALT+0010/0013 solution, but it didn't help. Any ideas would be very appreciated! -B -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cartridge Returns - How to remove
Thanks. It worked like a charm!
"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.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. 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.) Outlook, eh? wrote: Greetings, I have spent quite a lot of time researching and trying out ways to find and remove cartridge return "indicators" (small boxes) from the end of email addresses in a spreadsheet to no avail. I have tried the Find/Replace with ALT+0010/0013 solution, but it didn't help. Any ideas would be very appreciated! -B -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove carriage returns from a cell | Excel Worksheet Functions | |||
Remove carriage returns in an Excel document? | Excel Discussion (Misc queries) | |||
how to remove hard returns, which show as little boxes | Excel Discussion (Misc queries) | |||
how do I remove Carriage Returns from a column in a spreadsheet? | Excel Discussion (Misc queries) | |||
How do I automatically remove carriage returns in Excel? | Excel Discussion (Misc queries) |