+ Reply to Thread
Results 1 to 12 of 12

Inserting Line Breaks With Formula

  1. #1
    Registered User
    Join Date
    05-30-2004
    Posts
    63

    Inserting Line Breaks With Formula

    I have a column with hundreds of rows.

    In that column, each row has an article. The article contains many paragraphs.

    I want to insert a line break where each sentence ends and 2 line breaks where each paragraph ends.

    I need to do this so I can import the whole spreadsheet into my site where the article will be displayed properly as per the formatting done within excel.

    I know CHAR(10) adds a line break, but how do I do this with multiple paragraphs?
    The reason why I think its a problem is because I know you have to have speech marks around the rest of the text before and after each line break.

    Furthermore, you need to start the article with a = sign.

    Have I understood it wrong or is there a better way?

    Thanks,

    JH
    Using Excel for Mac
    But mostly use Google Sheets

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Inserting Line Breaks With Formula

    What designates a paragraph?

    I don't know what you mean about speech marks and =, try inputting in a cell without, you will see that they are not needed.

  3. #3
    Registered User
    Join Date
    05-30-2004
    Posts
    63

    Re: Inserting Line Breaks With Formula

    So, if I type in:

    The Quick Brown Fox Jumps &CHAR(10)& Over The Lazy Dog

    Will it work without any speech marks or = sign at the beginning?

    I've tried it this way and it doesnt work.

    My cell is set to wrap.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Inserting Line Breaks With Formula

    you could probably do all that in word!
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Inserting Line Breaks With Formula

    Quote Originally Posted by Jetheat View Post
    So, if I type in:

    The Quick Brown Fox Jumps &CHAR(10)& Over The Lazy Dog

    Will it work without any speech marks or = sign at the beginning?

    I've tried it this way and it doesnt work.

    My cell is set to wrap.
    I see what you mean now. No it won't, but if you type

    The Quick Brown Fox Jumps

    and then Alt-Enter and then

    Over The Lazy Dog

    and then Enter, you will get what you want.

  6. #6
    Registered User
    Join Date
    05-30-2004
    Posts
    63

    Re: Inserting Line Breaks With Formula

    I need to do this in a formula coz I have hundreds of rows and I cant go through each article on each row sorting out the line breaks.

    Is there a simpler way?

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Inserting Line Breaks With Formula

    you could try find /replace
    find .
    replace
    .alt+010

  8. #8
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Inserting Line Breaks With Formula

    Then I repeat, What designates a paragraph?

    A sentence end can be guessed at by a full stop followed by space, but I know no rule for paragraphs.

  9. #9
    Registered User
    Join Date
    05-30-2004
    Posts
    63

    Re: Inserting Line Breaks With Formula

    A paragraph could be designated as at least 2 spaces after a full stop while a sentence would be just one space. or even a hard return.

    I'm using a mac. What does alt 010 do?

  10. #10
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Inserting Line Breaks With Formula

    inserts char 10

  11. #11
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Inserting Line Breaks With Formula

    Try

    =SUBSTITUTE(SUBSTITUTE(A2,". ","."&CHAR(10)),". ","."&CHAR(10))

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Inserting Line Breaks With Formula

    Or if you want carriage returns at the paragraph breaks...

    =SUBSTITUTE(SUBSTITUTE(A1,". ","."&CHAR(10)&CHAR(10)),". ","."&CHAR(10))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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