Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Hard Return; Special Character
I'm looking for a simple macro that deletes all hard returns in all cells in
a large worksheet. These hard returns originally came from Outlook's BCM. In Excel, the characters look like a small square, and when copied and pasted into Word, they look like this "^" or even this "^l". Does anyone have any idea how to delete all such characters on a sheet, or all such characters in a specified range (A1:CA6000). Regards, Ryan-- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Hard Return; Special Character
Sub Remove_CR_LF()
With Selection ..Replace What:=Chr(160), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(10), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub Gord Dibben MS Excel MVP On Tue, 2 Oct 2007 14:09:29 -0700, ryguy7272 wrote: I'm looking for a simple macro that deletes all hard returns in all cells in a large worksheet. These hard returns originally came from Outlook's BCM. In Excel, the characters look like a small square, and when copied and pasted into Word, they look like this "^" or even this "^l". Does anyone have any idea how to delete all such characters on a sheet, or all such characters in a specified range (A1:CA6000). Regards, Ryan-- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Hard Return; Special Character
no macro needed:
1. click on A1 2. pull-down: Edit Replace in the Find what block touch CNTRL-j leave the Replace with block empty click Replace all -- Gary''s Student - gsnu200748 "ryguy7272" wrote: I'm looking for a simple macro that deletes all hard returns in all cells in a large worksheet. These hard returns originally came from Outlook's BCM. In Excel, the characters look like a small square, and when copied and pasted into Word, they look like this "^" or even this "^l". Does anyone have any idea how to delete all such characters on a sheet, or all such characters in a specified range (A1:CA6000). Regards, Ryan-- -- RyGuy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Hard Return; Special Character
Thanks for the look! Gary''s Student, I know this trick, and Ive used it
successfully many times in the past. Unfortunately, it does not work in this instance. Gord Dibben, I tried your macro. I had to modify it a bit. I ran the following: Sub Remove_CR_LF() With Selection Replace What:=Chr(160), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub I got a message saying €œCompile Error: Named argument not found.€ Any more ideas? TIA, Ryan-- -- RyGuy "Gord Dibben" wrote: Sub Remove_CR_LF() With Selection ..Replace What:=Chr(160), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(10), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub Gord Dibben MS Excel MVP On Tue, 2 Oct 2007 14:09:29 -0700, ryguy7272 wrote: I'm looking for a simple macro that deletes all hard returns in all cells in a large worksheet. These hard returns originally came from Outlook's BCM. In Excel, the characters look like a small square, and when copied and pasted into Word, they look like this "^" or even this "^l". Does anyone have any idea how to delete all such characters on a sheet, or all such characters in a specified range (A1:CA6000). Regards, Ryan-- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Hard Return; Special Character
Take a look at Gord's suggestion once more.
You'll notice that he had a dot in front of "Replace" (".Replace"). That means that this method is refering to the object in the previous "with" statement--in this case, it's the Selection. So add that dot back! ryguy7272 wrote: Thanks for the look! Gary''s Student, I know this trick, and Ive used it successfully many times in the past. Unfortunately, it does not work in this instance. Gord Dibben, I tried your macro. I had to modify it a bit. I ran the following: Sub Remove_CR_LF() With Selection Replace What:=Chr(160), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub I got a message saying €œCompile Error: Named argument not found.€ Any more ideas? TIA, Ryan-- -- RyGuy "Gord Dibben" wrote: Sub Remove_CR_LF() With Selection ..Replace What:=Chr(160), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(10), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub Gord Dibben MS Excel MVP On Tue, 2 Oct 2007 14:09:29 -0700, ryguy7272 wrote: I'm looking for a simple macro that deletes all hard returns in all cells in a large worksheet. These hard returns originally came from Outlook's BCM. In Excel, the characters look like a small square, and when copied and pasted into Word, they look like this "^" or even this "^l". Does anyone have any idea how to delete all such characters on a sheet, or all such characters in a specified range (A1:CA6000). Regards, Ryan-- -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Hard Return; Special Character
Gord and Dave, you are 100% correct! Your guidance was great!! I think I
(somehow) became the victim of word wrap. The macro worked great when I took another look and actually tried to understand what was going on!! The more I learn, the more I realize there is a lot left to learn!! Regards, Ryan--- -- RyGuy "Dave Peterson" wrote: Take a look at Gord's suggestion once more. You'll notice that he had a dot in front of "Replace" (".Replace"). That means that this method is refering to the object in the previous "with" statement--in this case, it's the Selection. So add that dot back! ryguy7272 wrote: Thanks for the look! Gary''s Student, I know this trick, and I€„¢ve used it successfully many times in the past. Unfortunately, it does not work in this instance. Gord Dibben, I tried your macro. I had to modify it a bit. I ran the following: Sub Remove_CR_LF() With Selection Replace What:=Chr(160), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub I got a message saying €œCompile Error: Named argument not found.€ Any more ideas? TIA, Ryan-- -- RyGuy "Gord Dibben" wrote: Sub Remove_CR_LF() With Selection ..Replace What:=Chr(160), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(10), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub Gord Dibben MS Excel MVP On Tue, 2 Oct 2007 14:09:29 -0700, ryguy7272 wrote: I'm looking for a simple macro that deletes all hard returns in all cells in a large worksheet. These hard returns originally came from Outlook's BCM. In Excel, the characters look like a small square, and when copied and pasted into Word, they look like this "^" or even this "^l". Does anyone have any idea how to delete all such characters on a sheet, or all such characters in a specified range (A1:CA6000). Regards, Ryan-- -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return True if cell contains special character | Excel Discussion (Misc queries) | |||
Delete special character | Excel Discussion (Misc queries) | |||
Seeking the character code for entering a Hard Return in a cell? | Excel Discussion (Misc queries) | |||
Can I create a special character for the Character Map? | Excel Discussion (Misc queries) | |||
Function to return Character Position of Xth character within a string | Excel Programming |