Globally Replace a character with Carriage Return in Excel 1

[sxschech]

I imported a word table where I had to replace all the carriage returns in order to import the table into Excel 2000 in order to have each table row to come in as a single row in excel. When I imported the table without replacing the carriage return, the result was multiple rows and merged cells for each row in the word table and this is why I ended up replacing the returns. The version of Excel (2000) I'm using does not seem to have the More button which allows identifying a carriage return. I would like to globally replace the ~ character with a carriage return. I chose the ~ character as this did not appear in the word table's text, so seemed like a good one to use as a unique identifier.

 

[xlbo]

You need code for this

in excel, press Alt+F11 - this will open up the VBE
Follow Insert>Module and copy and paste this in:

Sub ReplaceEm()
Selection.Replace _
What:="~~", Replacement:=Chr(10), _
SearchOrder:=xlByColumns, MatchCase:=True

End Sub

Go back to excel and select the data, then go Tools>Macro>Run

Select the sub and run it

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[sxschech]

Geoff,

Thanks so much for your code and step by step instructions. The code worked great, however, it had a problem when the cell contained the max number of characters. So ended up having to run the macro in chunks by highlighting a range of 20 rows and repeating.

Can I assume that if I need to strip out the carriage returns, I would just reverse the What and Replacement so that What has chr(10) and Replacement has "~~" ?

 

[xlbo]

spot on but I would use something other than a ~ (tilda)
You will notice that the code had ~~ in it as a tilda is used to force excel to look for the EXACT character - it is used to find wildcards like *

so find("*&quot
will find all text
find("~*&quot
will find *

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[TonyJollans]

Hi sxschech,

I would second Geoff in saying use a character other than the tilde but, one very minor point. If you want to change back you would only put a single tilde in the replacement string - it's only in the 'What' string that it needs to be doubled.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[sxschech]

Thanks to you both. What character would you suggest I use to represent a carriage return that would be unique, perhaps the pipe | as the text could contain other symbols which I wouldn't want to confuse with the one representing the cr.

 

[xlbo]

Pipe would be ok - I don't think it has any special significance to excel

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[sxschech]

Follow-up question...When I ran the code, it changed text that was coloured differently to black. The cell contained a mixture, a paragraph in black text, with a sentence comment in red. Is there some additional code I would need in order to preserve the coloured comments?

 

[xlbo]

Without looping through every single character in the text and checking what colour it is....no I'm afraid

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[sxschech]

OK. Thanks.