Search & Replace to insert alt+<enter>

[wennerberg]

Can I do a search and replace to place an alt+<enter> in text in a range
of cells. Data example is "Phoenix (SOUTH)". I always want what is in
parenthesis on a line by itself in the cell, so I'd like to replace
"SPACE (" with "alt+<enter> ("
Any version of Excel, OK.
TIA

 

[Gord Dibben]

Edit>Replace

what: space

with: ALT + 0010 (from the numpad to the rightside of keyboard)

Replace all.


Gord Dibben Excel MVP

 

[Dave Peterson]

Another option:
select the range
edit|replace
what: (spacebar)
with: ctrl-j
replace all

(Ctrl-j might be easier to use and it's lots easier to describe! <vbg>)

 

[Roger Govier]

Hi Dave

I hadn't realised that Ctrl-j did the same thing as Alt+Enter.

For this task, not only is it easier to describe, but much easier to use
when you are on a notebook keyboard without easy access to the numeric pad!!

Regards

Roger Govier

 

[Gord Dibben]

Thanks Dave.

Much easier.

Where did you find CTRL + j


Gord

 

[Dave Peterson]

I believe it was a post by an MSWord MVP who answered a crossposted message who
suggested it.

It reminded me of the old DOS keyboard keystrokes.

doing this from the command prompt to create a text file:

copy con: c:\myfile.txt
some text here
some text here
ctrl-z (hit, don't type

That'll close the myfile.txt.

Same with some programs (not excel's edit|replace or edit|find dialog) for
ctrl-m (carriage return).

 

[wennerberg]

Thanks for the direction on this! It works well, but here is one
caveat:
If you replace a "real" character along with the ctl+j, the "real"
character does not display in the dialog box:

ORIGINAL STRING:
Washington D.C. (North)

CHANGE TO:
Washington D.C.
(North)

To do this I must include the left parenthesis because of multiple
spaces in the string, and therefore search for "<space>(" and replace
with "ctl+j("

The parenthesis do not show up in the Find and Replace dialog box in
"Replace with:" after entering the ctl+j or alt+0010 on num pad, but it
does keep the parenthesis. I just use my imagination.

 

[Dave Peterson]

If you look really close, you may see a little bit of the pixels--but the text
is wrapping in the replace dialog, too. (I can't see what's there, but I do see
just a bit of it.)