Line Breaks in Formulas

The CHAR() function can be used to represent a character in a cell if you know its ASCII number. For most characters, it’s a heck of a lot easier to use those letter keys on your keyboard, but it can be useful for some other characters.

For instance, if you want to put an address in cell on multiple lines, you can use CHAR(10) (10 being the ASCII code for a line feed).

Char10

If you get a bunch of squares and your text is all on one line, go to Format > Cells > Alignment and check the Wrap Text box.

To create a line feed while typing (i.e., not in a formula), Alt+Enter does the same thing.

Posted in Uncategorized

76 thoughts on “Line Breaks in Formulas

  1. Dick: I tried to solve a text wrap problem my wife was having by embedding several “CHAR(10)” characters inside of an “IF” formula, in order to create a pseudo “autofit” kind of situation for end users. The problem with embedding these characters (line feeds) inside of a formula is that after the formula is entered, when the formula is triggered to insert the line feeds (and thus change the row height accordingly), nothing happens. This is consistent with Excel’s behavior of not having a worksheet function change the state of Excel, but simply return a value. If, on the other hand, the line feeds are entered in during the creation of a formula (like your example), then Excel, upon hitting the enter key, does change the state of itself and modify the row height to meet the needs of the formula. Other than using VBA event triggers (which I can do, but my wife won’t understand), I don’t suppose there is a “passive” method for changing the row height predicated on user input. The issue concerns users entering text into a *merged* cell, and the need to automatically adjust the height of the row if wrapping occurs. Merged cells are a pain in the butt to work with, but it can’t be avoided in this case. Any help, anyone?

  2. I am attempting to force a line break within a cell while generating an excel worksheet from vb code (SalesLogix’ version of it). Vbcrlf, Chr(0010), Chr(10), Chr(10) & Chr(13), etc. all push the subsequent text to the next row instead of adding a line break within the cell. What I think I need is the ascii code for the key combination alt+0010 , or alt+enter. I have been looking for this without success.. Any ideas?

  3. Kgarvin: Chr(10) is the ASCII code for alt+0010 and alt+enter. It must be something peculiar to SalesLogix that is causing the problem. (I don’t know what SalesLogix is, so I can’t say for sure.)

    I wonder if you set the WrapText property to True first if that would trick it into doing the right thing.

  4. If the SalesLogix code creates a string containing various line ending techniques, then pastes the text into the cell, it will naturally use multiple rows to contain the text. This is equivalent to selecting a cell but not activating the cursor in the cell before pasting text.

    You need to use ActiveCell.Value = MyMultiLineString

  5. I found that embedding char(10) in a formula works only if the cell is formatted with Wrap Text. Otherwise it’s ignored. Note that this should be useful to those who are not using VBScript.

  6. I tried the formula =”a”&”[alt+enter]”&”b” , where “[alt+enter]” is the alt+enter keys in quotes. With text wrap on,it produces:
    a
    b
    on separate lines in the cell. works great thanks

  7. Hi,

    I would like to say thank you. You have shown me something that I can use in many formulas.

    Thank you,

    Rob

  8. Great hint, I had everything figured out but “wrap text” in cell formatting. Good to leave the text of all the replies on the page, that way Google has a better chance of finding it.

  9. Another sweet thing is non-breaking spaces.

    Consider the following phrase:
    a man a plan a canal panama

    Put that into a cell and format the cell with word wrap.
    Expand the column so it fits on one line. make sure the row height is such so that a few lines can be read at once.

    Then reduce the width slightly until ‘panama’ moves to the next line. An orphaned word like this may not be desirable.

    You can insert a “non-breaking space” between canal and panama.
    Enter edit mode, select the space between those two words. From the Insert menu, select Symbol.
    From the Special Characters tab, select a non-breaking space – click Insert
    So now it reads:
    a man a plan a
    canal panama

  10. I need help… ive merged multiple cells to make a “memo like” field, and ive referenced many rows in this field using the CHAR(10) and i’ve set the Word Wrap property to true in this field…

    The text is huge, and not all lines are wrapped… is there any restriction of char numbers in merged cells and word wrap property?

    I can send my .xls… I really need this help.

  11. Hello

    How do you insert a line using an if statement or something similar.

    I would like to insert a row if there is a number in say Row 15.So rather than clicking on insert row.

    Cheers

  12. Greetings,

    I found your little tutorial here very useful when I’m working with worksheets.

    However, I often use Excel to generate code for use in languages such as lua and python. The problem comes when I try to copy & paste the data from a cell with the linebreak character into a text file (with notepad or any text editor like ultraedit)

    Here’s an example formula as entered into excel:
    =”Value in cell A1 is ” & A1 & CHAR(10) & “Value in cell A2 is ” & A2

    Now the visual results in the cell itself:
    Value in cell A1 is Belial
    Value in cell A2 is Butcher

    and the results when pasted to notepad (or even this website):
    “Value in cell A1 is Belial
    Value in cell A2 is Butcher”

    As you can see, the process seems to change the whole thing into one big text string, which causes me some problems. To see how big a problem it is, try putting some quotes in the result, you will see that all single quotes become double quotes when you paste the data.

    Any help on this issue would be appreciated!

    Best Regards,
    Francis.

  13. Can soemone tell me how to wrap a merged cell so that the wrapping information is viewed without manually expanding the row to see the hidden information.

  14. Tom –

    Merged cells are pretty stupid about wrapping and autofitting row heights. (Same as textboxes in Excel, you can wrap the text, and then you lose the chance to autofit the box to the text.) I have seen huge macros written to try to control a wrapped merged cell’s row height, and they ain’t pretty.

    I did just come across a not-too-cumbersome workaround on ExcelTips:

    http://exceltips.vitalnews.com/Pages/T0985_Automatic_Row_Height_For_Merged_Cells_with_Text_Wrap.html

    – Jon

  15. Just spent a while trying to find the same answer. Finally got it :) The trick is to use Excel’s Substitute function.

    =SUBSTITUTE(A1,CHAR(10),” – “)
    This will swap any line breaks (or annoying squares) in A1 with a dash.

    Since your case looks similar to the one I was working on, you’ll probably have a ‘carriage return’ in there too (in addition to the line break). To fix them both, you can use the formula below. It basically removes the line-break and swaps for a dash, then finds the Carriage Return and swaps it out with nothin (“”):

    =SUBSTITUTE(SUBSTITUTE(A1,CHAR(10),” – “),CHAR(13),””)

    Hope this helps!!
    -Don

  16. chr(10) does not work. When I put it inside my cell value between two strings – it starts to wrap all contest of the cell according to other values in the same column. Sth like that:

    “hello my name is ” & Chr(10) & “ania”

    looks like this:

    hello
    my na
    me is
    ania

    Terrible!!!

  17. Wrapping is all or nothing. If you enable wrapping then not only will your Chr(10) wrap, but the text will also wrap anywhere else it gets too long for the cell width. And autofitting isn’t smart enough to make the column just wide enough for the widest single line within the cell.

  18. Thanks Don for your tip on removing line breaks and carriage returns! That problem’s been doing my head in for a while now. Cheers

  19. Don,

    Just wanted to say thanks as well… a problem that might have taken me several days mulling over took me less than 15 minutes googling.

    Thankyou Don, and Thankyou Google!

  20. The substitute function works very nicely to change a given character into another. Unfortunately, I find that it also removes any formatting within a cell – if individual words in a sentence are in bold, the bold will be lost if the substitute function is used on the text in that cell.

    Is there any way to retain rich text formatting when using this function?

  21. “is there any restriction of char numbers in merged cells and word wrap property?”

    I didn’t see anyone address this. In a single cell there is a 1024 character limit on word wrapping, viewing and printing. You can beat it with the hard return char(10) or alt+enter. This may be a hard limit regardless of whether the cell is merged or not, or it may be a cumulative limit (2 merged cells have a 2048 character limit. I haven’t played around with it to figure out.

  22. “As you can see, the process seems to change the whole thing into one big text string, which causes me some problems. To see how big a problem it is, try putting some quotes in the result, you will see that all single quotes become double quotes when you paste the data.”

    I’ve been having this same problem, and would love to hear if anyone solves it.

  23. In the database I have a field that saves value like “State Changed From : Alabama To : Arkansas
    Product Type List Changed From : Clothing; To : Clothing;Miscellaneous;
    “.

    If I replace the ”
    ” with “VBNewLine” in vb file then export it to excel file, the vbNewline will put the content after it in a new row in the excel file.

    But what I want is to display them in one table cell with new lines.

    I tried replace ”
    “, with CHAR(10),But it doesn’t work.Is there any other way that will cause excel to take a new line within the same cell (Equivalent of Alt + Enter).

    Thanks a lot!

  24. Sunny –

    All that’s in one field? Messy kind of database, eh?

    How are you putting the text with vbNewLines into the worksheet? The following one-liner from the Immediate Window put two-line strings into each of two side-by-side cells:

    ActiveCell.Resize(,2).Value = array(“abcde” & vbnewline & “fghij”, “12345? & vbnewline & “67890?)

  25. “As you can see, the process seems to change the whole thing into one big text string, which causes me some problems. To see how big a problem it is, try putting some quotes in the result, you will see that all single quotes become double quotes when you paste the data.”

    I have the same problem. Would appreciate if anyone could help on it.

    Thanks,

  26. Sunny perhaps it would help if you showed the code.

    for example try something like this…

    Sub EnterText()
      Dim rst As Recordset
      Dim rng As Range
      Set rng = ActiveCell
      Set rst = openrecordset‘……. blah blah blah Sunny’s database

      Do Until rst.EOF
        rng.Formula = Replace(rst(1), “/n”, vbLf)
        rng.WrapText = True
        rng = rng.Offset(1)
        rst.movenext
      Loop
    End Sub

    I butchered a bit of made up dao for the database but it should work in a similar way to the problem you are experiencing. I think it should work.

  27. Jan, thank you for your kind reply.

    I displayed the data in a gridview from the database, and then exported the data from the gridview to the excel file.

    I replaceed the ”
    ” with ” “(spaces), the data is in the same cell, and the client accepts this format for now.

  28. I have a question on separating cell content into multiple cells. For example,

    No 4
    Glendale Str
    London

    I want to make this into 3 different cells.

    Could someone help me with this? Thanks so much…

  29. I am having the same issue as JZ. I have a cell that I want to break into multiple cells. However, I don’t want to use fixed width because that will break words up. For example, one cell contains “El Paso Country Club”, but I want it to be converted to “El Paso” / “Country Club”, with something like a maximum of 14 characters per line, and if a word is split, then move it into the next column. Thanks

  30. Hi all,

    I have a similar problem but slight more difficult…

    I am building a 2D barcode scanning form which we want to use at a tradeshow.

    The barcode produces a string of data that looks like:

    John^Smith&^jsmith@acme.com^Sydney^NSW^Australia

    The data is all present but each field is separated by the ^ symbol.

    I want to pull this data and make it paste into a single cell-row each such as:

    John
    Smith
    jsmith@acme.com
    Sydney
    NSW
    Australia

    Is this possible? :) Thank you so much for this opportunity to post.

    Regards,
    Brenden

  31. Hi Everyone, i’m using excel too help me format a huge long string of HTML code that will represent an icon on a google map based on a buch of survey data that we’ve collected and inputted to a spreadsheet. Everything works great except the line break issue.

    an example of a final assembled string is:
    “… Primary Fuel Type (Heating Only):Gas’,CDEAmember) map.addOverlay(marker);”

    I need to force excel to wrap the everything from ‘map.addoverlay’ to the next line in order to make the code read properly. Now, in excel i can get it to work the way it has been described above but when i copy and paste the string with the line break into a notepad screen with my HTML the linebreak is gone (even when ‘word wrap’ is enabled) and i get a little square box. Is there a way to get excel to paste accross two lines? I really need to make this work! Any help would be most appreciated! :)

    Iain.

  32. Cheers Don G!!!

    You are a lifesaver – just saved me a ton of aggravation.

    Next time you’re in Cleveland, OH I’ll buy you a cold one!

  33. Hi to all,

    Does any one have idea about clubbing all the excel file into one single file, with various sheets.

    Example.

    S.No Name
    1) A.xls
    2) B.xls
    3) C.xls
    4) D.xls
    5) E.xls

    Instead I need like this. File 1 with sheet A,B,C,D & E.

  34. can anyone tell me if its possible to swap 2 words around that are in the same cell.
    My problem is that I have a very large list of names (1000+) that are stored in an excel sheet. At the moment they are arranged by first name, last name. However I need to change them to last name first name. I could just cut and paste but that wouold take forever. The whole name is stored in the one cell. does anyone know if there is there a formula out there to do this?

    Thanks in advance

    Liz

  35. Hi Liz,

    Assuming A1 contains 2 words separated by a space this will swap the words.

    B1: =MID(A1,FIND(” “,A1)+1,LEN(A1)) & ” ” & LEFT(A1,FIND(” “,A1)-1)

  36. Thanks for that andy, but it doesnt seem to be working. The names start in A1 and go all the way down to A13 and there is a space between the two words. I’ve copied your formula into A2 and dragged it down to A13 to the end of the list of names. Any suggestions as to what i’m doing wrong :-)

  37. Andy apologies, thats what I meant last time. I had pasted it into B1 and dragged it down to B13 and it didnt work.i’ve tried pasting special values too. :(

  38. Andy,

    I’ve figured it out! I thought the “B1:” at the start of your formula was part of it so i was copying it in aswell Sorry!! Thanks a mill, it works great and has saved me a heap of work. Cheers :)
    Liz

  39. Same problem here .
    Want to export excel to CSV and import it to a website.
    The newlines are giving a lot of problems.

    This solutions works for me for most rows :

    1. Select the entire area in Excel that includes translatable cells, then Copy (ctrl C)
    2. Paste into an empty doc in Word (ctrl V)
    3. Use the Find and Replace (ctrl H) function, press “Special” and select “Manual line break”. There should be a “^l” appearing in the upper part of the Find and Replace dialogue (Replace.
    4. In the lower part (With, enter a single space.
    5. Press “Replace All”.
    6. Now all manual line breaks should be deleted in your text.
    7. Select the entire cell area and paste it back into Excel.

  40. Dear all,

    I have a small problem and I have a bigger problem. First the bigger problem is that I do not know too much about excel and know nothing about vb and all. The smaller problem is that I have some data which has variable names and variable descriptions on the second line. Now the variable description often has the defining codes. For eg. low income-1, high income-2, middle income-3. etc. I want these variable descriptions to be imported as variable labels into stata. So i do it for all in the do file. But the problem with a lot of codes is they have line breaks in between them. For eg. “nevermarried=1,
    currentlymarried=2,
    widowed=3,
    divorced/separated=4.”

    I need to do something so that all line breaks from the entire data are removed so i can easily paste them in the do file and label the variables.
    It would be great if anyone could help. Basically I just want to remove all line breaks from my data. And I want an easy way to do without having to do all of them manually.

    Regards,

  41. I have data with many line breaks. How do I replace three line breaks with one? The formula below is a failed attempt.

    =SUBSTITUTE(A1,CHAR(10)&CHAR(10)&Char(10),Char(10))

  42. Update to comment above. I used:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,” “,” “),CHAR(10),”#”),” #”,”#”),”###”,”##”),”###”,”##”),”###”,”##”),”#”,CHAR(10))

  43. Anjor,
    One easy way to remove line breaks is to use the Data…Text to Columns menu item with the line feed character as the delimiter. If you do this, you will end up with each code in a separate column.

    To do it:
    1) Make sure that you have enough blank columns adjacent to your data. The parsed data will overwrite any pre-existing data in those adjacent columns.
    2) Select the data to be parsed
    3) Open the Data menu, and choose the Text to Columns item
    4) At the first step of the resulting wizard, choose Delimited, then hit the Next button
    5) At the second step of the wizard, check the box for Other, then click in the field next to it. Hold the ALT key down, then type 0010 using the numeric keypad. The numbers over QWERTY won’t work for this purpose!
    6) Click Finish

    Brad

  44. Hi all,

    I have a problem with line breaks desplaying as little squeres instead of line breaks.

    I have Excel 2002,2003,2007 and all of them display line breaks properly but my clients with Excel 2007 and older have this problem. Pretty anoying.

    Is there some setting in excel options that display line breaks as those symbols? I’ve looked in all my versions and can’t find it.

    Any ideas anybody?

    Thanks

    Paul

  45. Thanks Tushar,

    I did create a formula for the addresses in my spreadsheet. Like “=addressFirstline&char(10)&addressSecondLine&char(10)&….” and it fixed the symbols inside the addresses.

    I still do have a problem though. (I have a cell that needs to hold a lot of info …multiline. Usually the users have a description of services that they have in some word document and they just need to copy and paste it into the cell. I made a simple form with one textbox with multiline turned on, so they can do that quite easily. However every time they hit enter the corresponding cell displays the little square symbol at the end of each line. (pretty annoying.)

    I need to write a macro which will look at each line of the textbox and while updating the cell replacing the line break symbol with “char(10)” and each line of text put in quotes and creating a formula.

    I tried the “vbcrlf” and it also returns the symbols but “char(10)” doesn’t. so the formula created by the macro needs to be something like:

    dim rng as range, myStr as string

    Set rng = range(“description”)

    myStr = “”
    myStr = myStr & “=” & me.TextBox1.rows(1).value & “&char(10)&”
    etc… ‘(I don’t think I can do that)

    ‘Or setup some loop:

    For x = 1 to me.TextBox1.rows.count

    myStr = myStr & me.TextBox1.rows(x).value & “&char(10)&”

    next

    I can’t figure out how to address a line portion of a textbox.

    Any ideas?

  46. Never mind, I got it!

    If anyone else has this problem…this works:

    Range(“A1”).Value = Replace(TextBox1, Chr(13), “”)

    works great even on the old Excel 2000 (that some of my clients still have).

    pretty simple

  47. I also need to paste carriage returns into notepad and I am getting the carriage return surrounded by quotes!!!!I can’t seem to fix this. I just want the text not the quotes surrounding it. I have to find and replace them in notepad, but some if my info has quotes in it as well and I want something automatic! Any help would make my day

  48. I am using a circular reference to test various data combinations. I need to break (terminate, end, stop) the circular reference when a new unique combination of data is generated. I have in mind to use an IF statement…

    Cell A1… =IF(A2=230, “[ESC]”, A2+1)
    Cell A2… =IF(A1=230, “[ESC]”, A1+1)

    NOTE: Calculations, Iterations ON, Setting 200
    NOTE: “[ESC]” represents your suggested command prompt…

    Thank you

  49. Shannon –

    Just as at the top, except use CHAR(13) instead of CHAR(10).

    Ascii 13 is the carriage return.

    …mrt

  50. Thanks to Smitha and Frank above.
    The Chr(10) worked excellent in my code wherein I needed a line feed after every comma in an output of my workbook.

  51. Hello all, I have a somewhat unique problem. I am using excel much like a content manager for a series of restaurant menus. I am linking the cells to text boxes in Word and I am formatting the text boxes using paragraph and text styles. The problem I am having is this, when I link the cell to the text box, word ads a carriage return at the top and bottom of the cell contents. Is there a way to avoid this?

  52. Mine seems to have a problem with doing this on a second worksheet. The line feed doesn’t seem to work when I add CHAR(10) in a second sheet.

    =”Runners Name: ”
    &CHAR(10)&A2&CHAR(5)
    &CHAR(10)&B2
    &CHAR(10)&”Birthdate”&TEXT(C2,”dd/mm/yyyy”)

    AND
    =A1&CHAR(10)&A2

    Both output single lines for me in Sheet2, but work on Sheet1. Any clue?

  53. In excel, I would like for the pressing of my enter/return key to ALWAYS result in a new line of text within the same cell. Is there any way/formatting/formulas to accomplish this throughout the entire sheet, workbook etc., as opposed to using a shortcut, or otherwise, every single time I want to accomplish this result? Thanks


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *