How do I search and replace with a line break?

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

 

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[SupernovaXIX]

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.

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)