replace text with special characters (2000/9.0.2720)

Topic

New Reply

After importing a text file in excel 2000, I want to insert a “new line” character in select cells.

Using MS WORD, the find/replace function supports special characters. How can this be done using excel or VBA?

For example, if a single cell contains the following text:
My sentence 1. My Sentence 2. My Sentence 3.

I want the cell to look like this (cell format using “wrap text” option) :

My sentence 1.

My Sentence 2.

My Sentence 3.

*********************************
Thanks in advance, John

Reply | Quote

Replies

For each cell ou want to find . and replace with . chr(10)

chr(10) gives you the soft return within a cell.

Reply | Quote

Select the cell.
Use Ctrl-H to bring up Excel’s find and replace Dialog.
In the ‘find’, type the fullstop.
Tab to the ‘replace with’ box,
Hold the Alt key and type 010 on your numeric keypad. You’ll see nothing appear, but don’t worry.
Select ‘replace All’
Voilla!

regards

zeddy

Reply | Quote

Hey – That GREAT! I will use this a lot.

Is there any way to “type it in” as I type the contents?

e.g., something like >> P A R A 1 [Space] 1 [Alt+0] [Alt+1] [Alt+0] P A R A 2 ???

I can add a macro LineBreak to the normal template – but it would usually be more convenient to just type it in…

Thanks, thanks, thanks

Reply | Quote

Alt-Enter puts a ‘hard carriage return’ in a cell (its in the Help file under ‘entering text’

Reply | Quote

I was lazy – I didn’t even think to check “help” – boo on me. The idea that “it couldn’t be done” had just seeped into my subconscious over the years. You were right – it was easy to find in help. Thanks

Reply | Quote