+ Reply to Thread
Results 1 to 7 of 7

Enter Line-Break [Alt + Enter] at specific location

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Enter Line-Break [Alt + Enter] at specific location

    I know Excel is not the best tool for formatting sentences, but is there a way to insert a Line-Break based on a condition? (Either using Formula or VBA).

    The situation is:
    • A fixed-width cell, is a concatenation of 5 strings. Each of these 5 strings is a small sentence of 3-6 words; individually much smaller than the cell-width.
    • 2 of these 5 are fixed (one at start, one at the end). The other 3 are results of their respective "IF" formulas.
    • Hence, the final sentence wraps over 2-3 lines.
    • I need the 3rd & 5th sentence (the whole sentence, all 3-6 words of it) to start in a new line, only if they are going to be wrapped into two lines.
    • Is this possible?

    So If the wrapping results in:
    String1 String2 Stri
    ng3 String4 Stri
    ng5


    Then change it to:
    String1 String2
    String3 String4
    String5

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Enter Line-Break [Alt + Enter] at specific location

    if cell is A1
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Enter Line-Break [Alt + Enter] at specific location

    actually
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    will do

  4. #4
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Enter Line-Break [Alt + Enter] at specific location

    Thanks for your responses.

    However, there are two issues here:
    1. A line-break should be inserted only if the string would not have fit in one line.
    2. Each string itself comprises of 3-6 words; and is the result of an "IF" formula - so the resulting strings lengths maybe different and number of spaces in each string may differ.

    I think my previous example was inappropriate with regards to point 2. A String in my case can be "S tr ing" (3 words) or "S t r i n g" (6-words) or even 4 or 5 words; So the "Instance_Num" in the "Substitute" formula needs to be dynamic (or maybe I can prefix a special character to the start of each string, and then substitute that character instead of space).

    But first - how do I check if a string is going to wrap itself over 2 lines or not? Any ideas.

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Enter Line-Break [Alt + Enter] at specific location

    i think you will definitely need to go down VBA route with this then
    please attach some sample file for working

    the problem i see with what you are asking is
    depending on what screen you view it on and zoom and column width
    the results may vary

    is the column width going to be fixed?
    what actually seperates your strings if it isnt space?

    maybe some sort of rowheight check to find out how many rows its wrapping?

    Please Login or Register  to view this content.
    maybe not selection change...but some other sort of macro that uses this concert of rowheight?
    unsure of how many lines you are talking about
    Last edited by humdingaling; 06-24-2014 at 11:54 PM.

  6. #6
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Thumbs up Re: Enter Line-Break [Alt + Enter] at specific location

    Mate,
    That was one brialliant idea - to check the "RowHeight" for a given column width.

    Here's how I achieved the desired outcome:
    • I prefixed a "#" at the start of all the multiple-word strings.
    • Then I created an extra column (a Pseudo-Column of exact column width). I use this pseudo-column to check any change in row-height before and after a string is concatenated.
    • So, if the row height increased, it meant that the last added string was wrapping over on two lines; and I then substituted the "#" with Char(10).
    • If not, that meant the new string was not wrapping and then I simply substituted the "#" with a space "_".
    • Finally, just delete the pseudo-column.

    It is not the most elegant solution, but it works .
    Thanks again!!

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Enter Line-Break [Alt + Enter] at specific location

    not a problem
    glad to have helped

+ 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] VBA to insert line break (Alt+Enter)
    By TextMonkey in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-12-2014, 01:18 PM
  2. [SOLVED] Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place
    By jaimelwilson in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-04-2013, 10:23 PM
  3. ALT+ENTER = line break
    By Nyla in forum Excel General
    Replies: 5
    Last Post: 05-23-2006, 05:45 PM
  4. [SOLVED] Why can't I get 'alt enter' to create a line break in Excel?
    By babbott in forum Excel General
    Replies: 3
    Last Post: 05-08-2006, 07:25 PM
  5. [SOLVED] How do I enter a line break within a cell?
    By Striving for efficiency in forum Excel General
    Replies: 4
    Last Post: 11-17-2005, 04:15 PM

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