Tags: char, character, comma, commas, excel, microsoft, msdn, newline, software, substitute

How to substitute a comma with a newline char using Replace.

On Microsoft » Microsoft Excel

10,000 words with 9 Comments; publish: Thu, 22 May 2008 18:11:00 GMT; (30678.00, « »)

I am trying to replace all commas with a newline character. What do I put in

the Replace field?

All Comments

Leave a comment...

  • 9 Comments
    • 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" <edspyhill01.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:B105C75F-CCBD-42E1-9A23-6D57AC8BF64A.excel.todaysummary.com.microsoft.com...

      I am trying to replace all commas with a newline character. What do I put

      in

      the Replace field?

      #1; Thu, 22 May 2008 18:13:00 GMT
    • 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" <edspyhill01.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:B105C75F-CCBD-42E1-9A23-6D57AC8BF64A.excel.todaysummary.com.microsoft.com...

      > I am trying to replace all commas with a newline character. What do I put

      > in

      > the Replace field?

      --

      Dave Peterson

      #2; Thu, 22 May 2008 18:14:00 GMT
    • "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" <edspyhill01.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > > news:B105C75F-CCBD-42E1-9A23-6D57AC8BF64A.excel.todaysummary.com.microsoft.com...

      > > 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.excel.todaysummary.com.unix-mail, deliverable, mailer, ether, host, unix-mail.<domain>,

      user, user707.excel.todaysummary.com.unix-mail.<domain>

      To:

      user707.excel.todaysummary.com.unix-mail

      deliverable

      mailer

      ether

      host

      unix-mail.<domain>

      user

      user707.excel.todaysummary.com.unix-mail.<domain>

      Thank you,

      Ed S.

      #3; Thu, 22 May 2008 18:15:00 GMT
    • 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

      <edspyhill01.excel.todaysummary.com.discussions.microsoft.com> 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" <edspyhill01.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      >> > news:B105C75F-CCBD-42E1-9A23-6D57AC8BF64A.excel.todaysummary.com.microsoft.com...

      >> > 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.excel.todaysummary.com.unix-mail, deliverable, mailer, ether, host, unix-mail.<domain>,

      >user, user707.excel.todaysummary.com.unix-mail.<domain>

      >To:

      >user707.excel.todaysummary.com.unix-mail

      >deliverable

      >mailer

      >ether

      >host

      >unix-mail.<domain>

      >user

      >user707.excel.todaysummary.com.unix-mail.<domain>

      >Thank you,

      >Ed S.

      #4; Thu, 22 May 2008 18:16:00 GMT
    • 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" <edspyhill01.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:A1A7BC3E-5DAD-487C-90F5-6F46E9DBD817.excel.todaysummary.com.microsoft.com...

      "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" <edspyhill01.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > > news:B105C75F-CCBD-42E1-9A23-6D57AC8BF64A.excel.todaysummary.com.microsoft.com...

      > > 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.excel.todaysummary.com.unix-mail, deliverable, mailer, ether, host, unix-mail.<domain>,

      user, user707.excel.todaysummary.com.unix-mail.<domain>

      To:

      user707.excel.todaysummary.com.unix-mail

      deliverable

      mailer

      ether

      host

      unix-mail.<domain>

      user

      user707.excel.todaysummary.com.unix-mail.<domain>

      Thank you,

      Ed S.

      #5; Thu, 22 May 2008 18:17:00 GMT
    • 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.

      #6; Thu, 22 May 2008 18:18:00 GMT
    • 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

      #7; Thu, 22 May 2008 18:19:00 GMT
    • "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.

      #8; Thu, 22 May 2008 18:20:00 GMT
    • 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

      #9; Thu, 22 May 2008 18:21:00 GMT