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

Replace Line Feed



 
 
Thread Tools Display Modes
  #1  
Old February 16th 06, 12:51 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Replace Line Feed


If you format a cell to wrap text, you can artificially put in a Line
Feed with ALT+ENTER. I you format back to unwrap, the Line Feed command
is shown as a square.

I have a spread sheet with a lot of Line Feeds, and I want them
replaced with semicolons, but they are quite elusive and I cannot eg.
put them into the search and replace function.

Anyone has a hint?


NSV


--
nsv
------------------------------------------------------------------------
nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
View this thread: http://www.excelforum.com/showthread...hreadid=513147

Ads
  #2  
Old February 16th 06, 01:16 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Replace Line Feed

Here is a neat trick:

Use Find/Replace, but in the FindWhat field, hold down the ALT key type 010
and then release the ALT key. Enter your normal character in the Replace
field.

You can use this trick to replace a variety of non-type-able characters.
--
Gary''s Student


"nsv" wrote:

>
> If you format a cell to wrap text, you can artificially put in a Line
> Feed with ALT+ENTER. I you format back to unwrap, the Line Feed command
> is shown as a square.
>
> I have a spread sheet with a lot of Line Feeds, and I want them
> replaced with semicolons, but they are quite elusive and I cannot eg.
> put them into the search and replace function.
>
> Anyone has a hint?
>
>
> NSV
>
>
> --
> nsv
> ------------------------------------------------------------------------
> nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
> View this thread: http://www.excelforum.com/showthread...hreadid=513147
>
>

  #3  
Old February 16th 06, 01:35 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Replace Line Feed

If you prefer a formula method, as opposed to the cool method Gary describes,
you can use this formula. The example assumes the value containing the line
feeds is in cell A1

=SUBSTITUTE(A1,CHAR(10),";")
--
Kevin Backmann


"nsv" wrote:

>
> If you format a cell to wrap text, you can artificially put in a Line
> Feed with ALT+ENTER. I you format back to unwrap, the Line Feed command
> is shown as a square.
>
> I have a spread sheet with a lot of Line Feeds, and I want them
> replaced with semicolons, but they are quite elusive and I cannot eg.
> put them into the search and replace function.
>
> Anyone has a hint?
>
>
> NSV
>
>
> --
> nsv
> ------------------------------------------------------------------------
> nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
> View this thread: http://www.excelforum.com/showthread...hreadid=513147
>
>

  #4  
Old February 16th 06, 03:06 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Replace Line Feed

Even neater...

Use ctrl-j instead of alt-0010 (easier to describe and easier to do!)

Gary''s Student wrote:
>
> Here is a neat trick:
>
> Use Find/Replace, but in the FindWhat field, hold down the ALT key type 010
> and then release the ALT key. Enter your normal character in the Replace
> field.
>
> You can use this trick to replace a variety of non-type-able characters.
> --
> Gary''s Student
>
> "nsv" wrote:
>
> >
> > If you format a cell to wrap text, you can artificially put in a Line
> > Feed with ALT+ENTER. I you format back to unwrap, the Line Feed command
> > is shown as a square.
> >
> > I have a spread sheet with a lot of Line Feeds, and I want them
> > replaced with semicolons, but they are quite elusive and I cannot eg.
> > put them into the search and replace function.
> >
> > Anyone has a hint?
> >
> >
> > NSV
> >
> >
> > --
> > nsv
> > ------------------------------------------------------------------------
> > nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
> > View this thread: http://www.excelforum.com/showthread...hreadid=513147
> >
> >


--

Dave Peterson
  #5  
Old February 16th 06, 03:09 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Replace Line Feed

> Use ctrl-j instead of alt-0010

I never knew that . Pretty cool.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Dave Peterson" > wrote in message
...
> Even neater...
>
> Use ctrl-j instead of alt-0010 (easier to describe and easier
> to do!)
>
> Gary''s Student wrote:
>>
>> Here is a neat trick:
>>
>> Use Find/Replace, but in the FindWhat field, hold down the ALT
>> key type 010
>> and then release the ALT key. Enter your normal character in
>> the Replace
>> field.
>>
>> You can use this trick to replace a variety of non-type-able
>> characters.
>> --
>> Gary''s Student
>>
>> "nsv" wrote:
>>
>> >
>> > If you format a cell to wrap text, you can artificially put
>> > in a Line
>> > Feed with ALT+ENTER. I you format back to unwrap, the Line
>> > Feed command
>> > is shown as a square.
>> >
>> > I have a spread sheet with a lot of Line Feeds, and I want
>> > them
>> > replaced with semicolons, but they are quite elusive and I
>> > cannot eg.
>> > put them into the search and replace function.
>> >
>> > Anyone has a hint?
>> >
>> >
>> > NSV
>> >
>> >
>> > --
>> > nsv
>> > ------------------------------------------------------------------------
>> > nsv's Profile:
>> > http://www.excelforum.com/member.php...o&userid=26500
>> > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=513147
>> >
>> >

>
> --
>
> Dave Peterson



  #6  
Old February 16th 06, 03:15 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Replace Line Feed

I like that, too. Much easier to explain over the phone.

Chip Pearson wrote:
>
> > Use ctrl-j instead of alt-0010

>
> I never knew that . Pretty cool.
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
> "Dave Peterson" > wrote in message
> ...
> > Even neater...
> >
> > Use ctrl-j instead of alt-0010 (easier to describe and easier
> > to do!)
> >
> > Gary''s Student wrote:
> >>
> >> Here is a neat trick:
> >>
> >> Use Find/Replace, but in the FindWhat field, hold down the ALT
> >> key type 010
> >> and then release the ALT key. Enter your normal character in
> >> the Replace
> >> field.
> >>
> >> You can use this trick to replace a variety of non-type-able
> >> characters.
> >> --
> >> Gary''s Student
> >>
> >> "nsv" wrote:
> >>
> >> >
> >> > If you format a cell to wrap text, you can artificially put
> >> > in a Line
> >> > Feed with ALT+ENTER. I you format back to unwrap, the Line
> >> > Feed command
> >> > is shown as a square.
> >> >
> >> > I have a spread sheet with a lot of Line Feeds, and I want
> >> > them
> >> > replaced with semicolons, but they are quite elusive and I
> >> > cannot eg.
> >> > put them into the search and replace function.
> >> >
> >> > Anyone has a hint?
> >> >
> >> >
> >> > NSV
> >> >
> >> >
> >> > --
> >> > nsv
> >> > ------------------------------------------------------------------------
> >> > nsv's Profile:
> >> > http://www.excelforum.com/member.php...o&userid=26500
> >> > View this thread:
> >> > http://www.excelforum.com/showthread...hreadid=513147
> >> >
> >> >

> >
> > --
> >
> > Dave Peterson


--

Dave Peterson
  #7  
Old February 16th 06, 03:33 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Replace Line Feed


Thanks - I'm quite familiar with the ALT + nnnn for writing , , ,
etc., but the 010 code was new to me. I looked in the character map for
a LF code, and didn't find anything. And also the CTRL + j was quite
cool. Where did you find those codes?


--
nsv
------------------------------------------------------------------------
nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
View this thread: http://www.excelforum.com/showthread...hreadid=513147

  #8  
Old February 16th 06, 04:37 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Replace Line Feed

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

You could also look at Windows CharMap.exe
windows start button|run|charmap and hit enter

Or
windows start button|program files|accessories|...

The ctrl-j is a hold over from before windows--the old DOS and DOS programs used
those kinds of keys.

nsv wrote:
>
> Thanks - I'm quite familiar with the ALT + nnnn for writing , , ,
> etc., but the 010 code was new to me. I looked in the character map for
> a LF code, and didn't find anything. And also the CTRL + j was quite
> cool. Where did you find those codes?
>
> --
> nsv
> ------------------------------------------------------------------------
> nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
> View this thread: http://www.excelforum.com/showthread...hreadid=513147


--

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
How do I insert a line feed in a formula? TX_KniveS Excel Worksheet Functions 5 October 31st 08 12:55 AM
How can I insert a line feed in the text in an EXCEL cell? Antionio Excel Discussion (Misc queries) 2 February 15th 06 10:59 AM
How to put line feed in a cell using a character not Alt/Enter David Crowther Excel Discussion (Misc queries) 1 October 3rd 05 06:59 PM
Adding line breaks by using find and replace soozie Excel Discussion (Misc queries) 3 March 6th 05 11:04 PM
Find/Replace carriage return & line feed characters in Excel. Mary Cullen Excel Worksheet Functions 1 January 4th 05 07:39 PM


All times are GMT +1. The time now is 06:52 AM.


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