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
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
>
>
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
>
>
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
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
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?
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks