How do I search and replace with a line break?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Want to use Ctrl-H to replace a specific character in a range of cells with a
line break (Alt+Enter) - how do I access special characters? (^p didn't work)
 
In the "Find What" box of the Replace dialog, hold down your left ALT key
and type 0010 on the numeric keypad (not the number keys above the letters).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
May well work but using a laptop

Chip Pearson said:
In the "Find What" box of the Replace dialog, hold down your left ALT key
and type 0010 on the numeric keypad (not the number keys above the letters).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
Click to expand...
 
use ctrl-j instead.

But most laptops have some sort of Fn key that will make the keys act as number
pad keys.

(but ctrl-j is easier <bg>.)
 
I'm trying the reverse of this issue. I have cells that has some kind of
special character in it. I'm assuming its a return but I don't know for
certain. I don't have access to the original data. I want to replace whatever
it is with a space. I tried the looking for alt 0010, alt 0013, alt 0015 ( 3
seperate finds) but nothing was found. is there a way I can find out exactly
what special characters this thing is?

Excel 2000

Thanks
John
 
It might be the non-breaking space character, 160.

If it is in, say, the fifth character position in cell A1, then try
this to find out its code:

=CODE(MID(A1,5,1))

Adjust the cell reference and the 5 to suit.

Hope this helps.

Pete
 
Thanks Pete, I'm halfway there. using your formula is see 13 in the position
of the special character. However when I do a find Alt 0013 using the left
alt and the number pad all it finds is cells that are blank. I know its only
the one space I''m looking at because when I use the formula for the space
before or after the special space I get the ascii value of a valid letter.
 
I'm not quite sure what your question means, but you can also use ctrl-j instead
of alt-0010 in both the edit|replace dialog as well as the Other character in
the data|text to columns dialog.
 
Thanks Dave, I found the answer! :)

Assuming your data starts at A1, put the following formula in B1:

=SUBSTITUTE(A1," ","~")

In between the empty quotes, instead of a space hit Alt-Enter.

Copy the formula down to match your list of data. Then select all of the
formulas and Copy, then Paste / Special / Values.

Now do a Text To Columns using "~" as the delimiter.
 
This may be easier:

=SUBSTITUTE(A1,char(10),"~")

But you should be able to use alt-0010 or ctrl-j in that text to columns
dialog.
 
Yay!

I think I love you, Chip Pearson. Thank you - you saved me from manually replacing every html break notation in a 21,000 post database.



Chip Pearson said:
In the "Find What" box of the Replace dialog, hold down your left ALT key
and type 0010 on the numeric keypad (not the number keys above the letters).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
Click to expand...
 
Back
Top