Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default How do I search and replace with a line break?

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)
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7,247
Default How do I search and replace with a line break?

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" wrote in message
...
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)



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default How do I search and replace with a line break?

May well work but using a laptop

"Chip Pearson" wrote:

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" wrote in message
...
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)




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default How do I search and replace with a line break?

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

Impish wrote:

May well work but using a laptop

"Chip Pearson" wrote:

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" wrote in message
...
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)





--

Dave Peterson
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 27
Default How do I search and replace with a line break?

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


"Chip Pearson" wrote:

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" wrote in message
...
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)






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default How do I search and replace with a line break?

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

On Feb 26, 8:15 pm, JohnH wrote:
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



"Chip Pearson" wrote:
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" wrote in message
...
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)- Hide quoted text -


- Show quoted text -



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default How do I search and replace with a line break?

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

On Mon, 26 Feb 2007 12:15:05 -0800, JohnH
wrote:

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


"Chip Pearson" wrote:

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" wrote in message
...
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)





  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 27
Default How do I search and replace with a line break?

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.

"Pete_UK" wrote:

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

On Feb 26, 8:15 pm, JohnH wrote:
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



"Chip Pearson" wrote:
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" wrote in message
...
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)- Hide quoted text -


- Show quoted text -




  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 144
Default How do I search and replace with a line break?

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

"Chip Pearson" wrote:

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" wrote in message
...
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)




  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default How do I search and replace with a line break?

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 wrote:

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

"Chip Pearson" wrote:

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" wrote in message
...
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)





--

Dave Peterson


  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 144
Default How do I search and replace with a line break?

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.


"Kathy" wrote:

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

"Chip Pearson" wrote:

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" wrote in message
...
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)




  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default How do I search and replace with a line break?

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.

Kathy wrote:

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.

"Kathy" wrote:

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

"Chip Pearson" wrote:

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" wrote in message
...
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)




--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
ALT+ENTER = line break Nyla Excel Discussion (Misc queries) 14 July 26th 07 03:07 AM
Search and Replace end of Line and Insert Alt-Enter dfresh34 Excel Discussion (Misc queries) 2 October 25th 06 06:09 PM
Break cell into multiple lines by line break Chia Excel Discussion (Misc queries) 1 August 20th 06 06:37 AM
Line Break Ramthebuffs Excel Discussion (Misc queries) 4 August 22nd 05 08:52 PM
line break in a cell luvgreen Excel Worksheet Functions 2 June 14th 05 06:48 PM


All times are GMT +1. The time now is 04:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"