+ Reply to Thread
Results 1 to 10 of 10

Replace "Comma Space" with New Line (Line Break(

  1. #1
    Registered User
    Join Date
    02-20-2021
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    8

    Replace "Comma Space" with New Line (Line Break(

    Hello,

    I have an excel file which has around 1500 rows of data. One of the column contains information as- City 1, City 2, City 3, City 4, City 5. Some of the cells has over 50 city names.


    I need the same data but with line break without comma as explained below-

    City 1
    City 2
    City 3
    City 4
    City 5

    Thank you!
    Attached Files Attached Files
    Last edited by Sam17; 03-06-2021 at 07:01 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2304
    Posts
    39,618

    Re: Replace "Comma Space" with New Line (Line Break(

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet, complete with some expected results.
    Glenn



  3. #3
    Registered User
    Join Date
    02-20-2021
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Replace "Comma Space" with New Line (Line Break(

    Hi Glenn,

    Thanks.

    Here's a sample image for your reference. I'm not able to upload a sample sheet here.
    Attachment 722066

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2304
    Posts
    39,618

    Re: Replace "Comma Space" with New Line (Line Break(

    You CAN upload a file. Everyone can. Please try again... I ask, because your requirement NOW seems to be the exact OPPOSITE of what you asked for in your first post.

  5. #5
    Registered User
    Join Date
    02-20-2021
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Replace "Comma Space" with New Line (Line Break(

    Hi Glenn,

    I'm so sorry.

    I got mixed up with different output.
    Attached Files Attached Files
    Last edited by Sam17; 03-06-2021 at 07:01 AM.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2304
    Posts
    39,618

    Re: Replace "Comma Space" with New Line (Line Break(

    In B2, copied down:

    =SUBSTITUTE(A2,CHAR(10),", ")
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,296

    Re: Replace "Comma Space" with New Line (Line Break(

    @Glenn,

    If the file the OP posted in Message #5 is correct, then you have the second and third arguments to the SUBSTITUTE function reversed. Of course, I may be wrong because the OP marked the question as being "SOLVED".

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2304
    Posts
    39,618

    Re: Replace "Comma Space" with New Line (Line Break(

    Hahaha. I have no idea... the requirement switched completely between Post 1 and attachment 1.

    In any event, as you said it's been marked as solved the OP has decided NOT to provide ANY feedback (how rude is that??!!) so we can not tell.

  9. #9
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,296

    Re: Replace "Comma Space" with New Line (Line Break(

    Quote Originally Posted by Glenn Kennedy View Post
    ...the OP has decided NOT to provide ANY feedback (how rude is that??!!) so we can not tell.
    Did the OP Rep you? I have found that sometimes an OP will put a thank you comment there instead of in the thread itself.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2304
    Posts
    39,618

    Re: Replace "Comma Space" with New Line (Line Break(

    Nope. Another "hit and run" poster.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Replace Line break with Comma without changing color format
    By Kinjal Doshi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-25-2020, 06:56 AM
  2. Replies: 4
    Last Post: 10-21-2020, 11:42 PM
  3. Help with "Insert a line break in a cell"
    By mrbret in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-30-2018, 05:20 PM
  4. line of visual basic to replace NA with "-" on sheets
    By JPSIMMON in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2014, 05:06 PM
  5. Replies: 0
    Last Post: 01-29-2014, 08:10 PM
  6. controlling the format of a "Line Break"
    By penfold1992 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-19-2013, 11:53 AM
  7. Break cell content into new line where "" exists
    By wali in forum Excel General
    Replies: 4
    Last Post: 12-29-2009, 08:11 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1