Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
replacing multiple Chr(13)s
How can I replace multiple occurences of Chr(13) with a
single Chr(10)? The cells I'm working with have more than one Chr(13) and I want to replace them with a single Chr(10). The cells looks like: some text Chr(13) Chr(13) some more text, more text Chr(13) Chr(13) Chr(13). Selection.Replace will let me change a single occurence of Chr(13) but I can't figure out how to get it to accept more than one. I can accomplish this by using the MID statement but that is very slow. All the multiple occurences are in a single column. Your assistance is greatly appreciated, Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
replacing multiple Chr(13)s
Hi Dan
One way: Replace 100 in a row with 99, 99 with 98 and so on down to 1. Pure horror from RAM's point of view -but RAM is pretty cheap those days: Sub test() Dim L As Long, S As String S = "A" & Chr(13) & Chr(13) & Chr(13) & Chr(13) _ & Chr(13) & "b" & Chr(13) & Chr(13) & Chr(13) & _ Chr(13) & Chr(13) & Chr(13) & Chr(13) & Chr(13) & "c" For L = 100 To 2 Step -1 S = Replace(S, String(L, 13), String(L - 1, 13)) Next S = Replace(S, Chr(13), Chr(10)) MsgBox S End Sub -- HTH. Best wishes Harald Excel MVP Followup to newsgroup only please. "Dan" wrote in message ... How can I replace multiple occurences of Chr(13) with a single Chr(10)? The cells I'm working with have more than one Chr(13) and I want to replace them with a single Chr(10). The cells looks like: some text Chr(13) Chr(13) some more text, more text Chr(13) Chr(13) Chr(13). Selection.Replace will let me change a single occurence of Chr(13) but I can't figure out how to get it to accept more than one. I can accomplish this by using the MID statement but that is very slow. All the multiple occurences are in a single column. Your assistance is greatly appreciated, Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing multiple instances of character | Excel Worksheet Functions | |||
Replacing cell reference on multiple sheets in one go.... | Excel Worksheet Functions | |||
Replacing formula with round multiple times | Excel Discussion (Misc queries) | |||
Replacing sheet reference in multiple cells... | Excel Worksheet Functions | |||
INTRICATE PROBLEM- How to find multiple text,excluding "H", in a multiple range of cells, then replacing 0 with another number in another cell | Excel Worksheet Functions |