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 to substitute a comma with a newline char using Replace.



 
 
Thread Tools Display Modes
  #1  
Old July 29th 06, 06:30 PM posted to microsoft.public.excel.misc
edspyhill01
external usenet poster
 
Posts: 4
Default How to substitute a comma with a newline char using Replace.

I am trying to replace all commas with a newline character. What do I put in
the Replace field?
Ads
  #2  
Old July 29th 06, 06:44 PM posted to microsoft.public.excel.misc
RagDyeR
external usenet poster
 
Posts: 3,574
Default How to substitute a comma with a newline char using Replace.

Hold down <Alt>,
And type
0010
Using the numbers on the Num keypad, *not* the numbers under the function
keys.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"edspyhill01" > wrote in message
...
I am trying to replace all commas with a newline character. What do I put
in
the Replace field?


  #3  
Old July 29th 06, 07:09 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default How to substitute a comma with a newline char using Replace.

Or use ctrl-j


RagDyeR wrote:
>
> Hold down <Alt>,
> And type
> 0010
> Using the numbers on the Num keypad, *not* the numbers under the function
> keys.
> --
>
> HTH,
>
> RD
> ================================================== ===
> Please keep all correspondence within the Group, so all may benefit!
> ================================================== ===
>
> "edspyhill01" > wrote in message
> ...
> I am trying to replace all commas with a newline character. What do I put
> in
> the Replace field?


--

Dave Peterson
  #4  
Old July 29th 06, 09:58 PM posted to microsoft.public.excel.misc
edspyhill01
external usenet poster
 
Posts: 4
Default How to substitute a comma with a newline char using Replace.



"Dave Peterson" wrote:

> Or use ctrl-j
>
>
> RagDyeR wrote:
> >
> > Hold down <Alt>,
> > And type
> > 0010
> > Using the numbers on the Num keypad, *not* the numbers under the function
> > keys.
> > --
> >
> > HTH,
> >
> > RD
> > ================================================== ===
> > Please keep all correspondence within the Group, so all may benefit!
> > ================================================== ===
> >
> > "edspyhill01" > wrote in message
> > ...
> > I am trying to replace all commas with a newline character. What do I put
> > in
> > the Replace field?

>
> --
>
> Dave Peterson
>


Neither solution worked. I'm using the "Find and Replace" menu under Edit
--> Replace. I have several fields in one cell separated by commas. I would
like to substitute a new line character for each comma to create a short list
in each cell.

Example:

From:
user707@unix-mail, deliverable, mailer, ether, host, unix-mail.<domain>,
user, user707@unix-mail.<domain>

To:
user707@unix-mail
deliverable
mailer
ether
host
unix-mail.<domain>
user
user707@unix-mail.<domain>

Thank you,

Ed S.
  #5  
Old July 29th 06, 10:26 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,911
Default How to substitute a comma with a newline char using Replace.

You followed these steps?

Selected a cell or cells.

Edit>Replace

What: enter a comma,

With: Hold the ALT key and type 0010(on numpad) or Dave's CTRL + j

Replace all.

Format the cell(s) to Wrap Text.


Gord Dibben MS Excel MVP

On Sat, 29 Jul 2006 13:58:01 -0700, edspyhill01
> wrote:

>
>
>"Dave Peterson" wrote:
>
>> Or use ctrl-j
>>
>>
>> RagDyeR wrote:
>> >
>> > Hold down <Alt>,
>> > And type
>> > 0010
>> > Using the numbers on the Num keypad, *not* the numbers under the function
>> > keys.
>> > --
>> >
>> > HTH,
>> >
>> > RD
>> > ================================================== ===
>> > Please keep all correspondence within the Group, so all may benefit!
>> > ================================================== ===
>> >
>> > "edspyhill01" > wrote in message
>> > ...
>> > I am trying to replace all commas with a newline character. What do I put
>> > in
>> > the Replace field?

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

>
>Neither solution worked. I'm using the "Find and Replace" menu under Edit
>--> Replace. I have several fields in one cell separated by commas. I would
>like to substitute a new line character for each comma to create a short list
>in each cell.
>
>Example:
>
>From:
>user707@unix-mail, deliverable, mailer, ether, host, unix-mail.<domain>,
>user, user707@unix-mail.<domain>
>
>To:
>user707@unix-mail
>deliverable
>mailer
>ether
>host
>unix-mail.<domain>
>user
>user707@unix-mail.<domain>
>
>Thank you,
>
>Ed S.


  #6  
Old July 29th 06, 10:33 PM posted to microsoft.public.excel.misc
RagDyeR
external usenet poster
 
Posts: 3,574
Default How to substitute a comma with a newline char using Replace.

You *did* say that you wanted this list to be in a *single* cell ... didn't
you?

If so, both suggestions will work "as advertised"!

After selecting your data, enter a comma in the "Find What" box,
And then in the "Replace With" box,
Follow Dave's (<Ctrl> <J>) suggestion or mine (<Alt> 0010).

Worked with the data you posted here.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"edspyhill01" > wrote in message
...


"Dave Peterson" wrote:

> Or use ctrl-j
>
>
> RagDyeR wrote:
> >
> > Hold down <Alt>,
> > And type
> > 0010
> > Using the numbers on the Num keypad, *not* the numbers under the

function
> > keys.
> > --
> >
> > HTH,
> >
> > RD
> > ================================================== ===
> > Please keep all correspondence within the Group, so all may benefit!
> > ================================================== ===
> >
> > "edspyhill01" > wrote in message
> > ...
> > I am trying to replace all commas with a newline character. What do I

put
> > in
> > the Replace field?

>
> --
>
> Dave Peterson
>


Neither solution worked. I'm using the "Find and Replace" menu under Edit
--> Replace. I have several fields in one cell separated by commas. I
would
like to substitute a new line character for each comma to create a short
list
in each cell.

Example:

From:
user707@unix-mail, deliverable, mailer, ether, host, unix-mail.<domain>,
user, user707@unix-mail.<domain>

To:
user707@unix-mail
deliverable
mailer
ether
host
unix-mail.<domain>
user
user707@unix-mail.<domain>

Thank you,

Ed S.


  #7  
Old July 30th 06, 12:19 AM posted to microsoft.public.excel.misc
edspyhill01
external usenet poster
 
Posts: 4
Default How to substitute a comma with a newline char using Replace.

It works. Must have been my sequence of selecting the cells and/or text.
Ctrl-J works the best, giving me one new line per comma. The Alt-0010 gives
me multiple new lines per comma but that is probably my error.

Thank you to all for the speedy help. This will save me hours of drudgery
formating test results for about 100,000 email aliases for pre and post
change comparisons from two SMTP servers.

Ed S.
  #8  
Old July 30th 06, 12:27 AM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default How to substitute a comma with a newline char using Replace.

ctrl-j and alt-0010 (from the number key pad--not above the QWERTY keys) are
equivalent and will give the same results.

I just find ctrl-j easier to explain (and use).

edspyhill01 wrote:
>
> It works. Must have been my sequence of selecting the cells and/or text.
> Ctrl-J works the best, giving me one new line per comma. The Alt-0010 gives
> me multiple new lines per comma but that is probably my error.
>
> Thank you to all for the speedy help. This will save me hours of drudgery
> formating test results for about 100,000 email aliases for pre and post
> change comparisons from two SMTP servers.
>
> Ed S.


--

Dave Peterson
  #9  
Old July 30th 06, 12:29 AM posted to microsoft.public.excel.misc
edspyhill01
external usenet poster
 
Posts: 4
Default How to substitute a comma with a newline char using Replace.



"edspyhill01" wrote:

> It works. Must have been my sequence of selecting the cells and/or text.
> Ctrl-J works the best, giving me one new line per comma. The Alt-0010 gives
> me multiple new lines per comma but that is probably my error.
>
> Thank you to all for the speedy help. This will save me hours of drudgery
> formating test results for about 100,000 email aliases for pre and post
> change comparisons from two SMTP servers.
>
> Ed S.


Figured out the multiple new lines. DOH! The last text typed into the
Replace field stays pre-loaded when recalled, but, not seeing the new line on
a retry, I would add another one.

Both solutions work perfect.

Thanks again,

Ed S.
  #10  
Old July 30th 06, 12:38 AM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default How to substitute a comma with a newline char using Replace.

Whew!

Glad you found the problem and solution. (It restores my faith in excel!)

edspyhill01 wrote:
>
> "edspyhill01" wrote:
>
> > It works. Must have been my sequence of selecting the cells and/or text.
> > Ctrl-J works the best, giving me one new line per comma. The Alt-0010 gives
> > me multiple new lines per comma but that is probably my error.
> >
> > Thank you to all for the speedy help. This will save me hours of drudgery
> > formating test results for about 100,000 email aliases for pre and post
> > change comparisons from two SMTP servers.
> >
> > Ed S.

>
> Figured out the multiple new lines. DOH! The last text typed into the
> Replace field stays pre-loaded when recalled, but, not seeing the new line on
> a retry, I would add another one.
>
> Both solutions work perfect.
>
> Thanks again,
>
> Ed S.


--

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
Using replace box to change a character into a CR (Alt-Enter char) Excel user Excel Discussion (Misc queries) 2 June 9th 06 12:26 PM
Replace or Substitute for COMBIN function Jaja Excel Discussion (Misc queries) 6 January 1st 06 02:18 PM


All times are GMT +1. The time now is 11:43 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.