A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do I search and replace with a line break?



 
 
Thread Tools Display Modes
  #1  
Old February 22nd 07, 01:44 AM posted to microsoft.public.excel.misc
Impish
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)
Ads
  #2  
Old February 22nd 07, 01:55 AM posted to microsoft.public.excel.misc
Chip Pearson
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  
Old February 22nd 07, 02:33 AM posted to microsoft.public.excel.misc
Impish
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  
Old February 22nd 07, 01:59 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
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  
Old February 26th 07, 08:15 PM posted to microsoft.public.excel.misc
JohnH
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  
Old February 26th 07, 08:23 PM posted to microsoft.public.excel.misc
Pete_UK
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  
Old February 26th 07, 08:47 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,912
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  
Old February 26th 07, 10:18 PM posted to microsoft.public.excel.misc
JohnH
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  
Old October 13th 08, 07:58 PM posted to microsoft.public.excel.misc
Kathy
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  
Old October 13th 08, 08:29 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 11:41 PM.


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