How do I search and replace with a line break?

[Impish]

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)

 

[Chip Pearson]

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)

 

[Impish]

May well work but using a laptop

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)

 

[Dave Peterson]

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>.)

 

[JohnH]

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

 

[Pete_UK]

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

 

[Gord Dibben]

Jihn

To really see what's in there, download Chip Pearson's CELLVIEW add-in.

http://www.cpearson.com/excel/CellView.htm


Gord Dibben MS Excel MVP

 

[JohnH]

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.

 

[Kathy]

Chip, Is it possible to change the line break to "Text to Columns"

 

[Dave Peterson]

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.

 

[Kathy]

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.

 

[Dave Peterson]

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.