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).
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.
Kevin:
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?
20 October 2004, 10:29 amKgarvin:
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?
24 November 2004, 7:40 amDick:
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.
24 November 2004, 10:04 amJon Peltier:
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
24 November 2004, 8:27 pmEdward Reid:
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.
28 December 2004, 11:40 amJohn Parslow:
This was right on target. Bullseye.
Thank-you. John
4 February 2005, 3:28 pmJohn Parslow:
I tried the formula =”a”&”[alt+enter]”&”b” , where “[alt+enter]” is the alt+enter keys in quotes. With text wrap on,it produces:
4 February 2005, 3:36 pma
b
on separate lines in the cell. works great thanks
Rob McIntyre:
Hi,
I would like to say thank you. You have shown me something that I can use in many formulas.
Thank you,
Rob
2 June 2005, 1:25 pmPaul Ritchie:
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.
14 June 2005, 11:53 pmSmitha:
Please let me know how to enter aline feed using vbscript while generating a excel sheet.
Thanks,
25 June 2005, 10:38 pmSmitha
Frank:
for VB
sub insertLineBreak()
activecell.formulaR1C1 = “=line 1″ & chr(10) & “line2 2″
end sub
13 July 2005, 10:14 amRob van Gelder:
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.
13 July 2005, 5:18 pmEnter 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
Murilo:
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.
22 July 2005, 12:19 pmS Matin:
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
7 August 2005, 6:32 pmF Lahaie:
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,
27 October 2005, 10:17 amFrancis.
Tom Q:
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.
7 November 2005, 4:06 pmJon Peltier:
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
7 November 2005, 7:22 pmThomas Blankson:
Hi,
Have been trying to figure out how to remove line breaks from a single cell.
eg:
Change:
No 4
Glendale Str
London
to read:
No 4 Glendale Street London.
Please advise
11 November 2005, 9:30 amDon Glasgow:
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!!
12 November 2005, 12:00 am-Don
ania:
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!!!
1 December 2005, 2:18 amJon Peltier:
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.
2 December 2005, 6:13 amClifford Barney:
Thanks to Edward Reid on the text-wrapping/CHAR(10) comment. Helped me ‘fix’ a formula that wasn’t working properly.
6 January 2006, 7:23 amNick Whiteacre:
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
14 February 2006, 6:32 pmrob:
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 February 2006, 3:50 amTom:
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?
3 March 2006, 12:20 pmColin:
“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.
14 March 2006, 8:52 amChris:
“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.
14 August 2006, 1:21 pmFloyd Rogers:
I am doing an inventory in excel and trying to get it generate a list of items to reorder when stock falls below a cernain number. Is that possible?
4 September 2006, 5:51 pmFloyd
Joshua:
Hi,
may i know how to wrap text within a if function???
18 October 2006, 9:51 pmSunny:
In the database I have a field that saves value like “State Changed From : Alabama To : Arkansas \nProduct Type List Changed From : Clothing; To : Clothing;Miscellaneous;\n”.
If I replace the “\n” 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 “\n”, 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!
25 October 2006, 6:44 pmJon Peltier:
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″)
26 October 2006, 4:58 amLori:
Sunny - Try Ctrl+j in the replace box.
26 October 2006, 7:47 amNhao:
“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,
27 October 2006, 1:28 pmsunny:
Jon Peltier & Lori
Thank you very much!
But it still doesn’t work.
30 October 2006, 10:48 amJan:
Sunny perhaps it would help if you showed the code.
for example try something like this...
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.
31 October 2006, 9:56 amsunny:
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 “\n” with “ “(spaces), the data is in the same cell, and the client accepts this format for now.
7 November 2006, 1:51 pmjz:
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 January 2007, 11:50 amGary:
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
2 March 2007, 12:11 pmBrenden:
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,
8 July 2007, 7:09 pmBrenden
Brenden:
Hi again... I found the solution:
http://office.microsoft.com/en-us/excel/HA011973581033.aspx
Just follow the steps and if you want to automat it, record it as a Macro and make a macro button in your Excel sheet. Many thanx!
8 July 2007, 7:21 pmIain M:
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.
16 July 2007, 1:56 pmDan V:
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!
22 August 2007, 8:56 amVK:
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.
24 August 2007, 3:06 amMarcus Bointon:
On Mac Office, you need to use CHAR(13) rather than CHAR(10). Why they couldn't abstract that away I don't know.
28 August 2007, 6:23 amLiz:
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
10 September 2007, 4:03 amAndy Pope:
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)
10 September 2007, 7:39 amLiz:
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
11 September 2007, 1:34 amAndy Pope:
Liz, try putting the formula in B1 and copying down to B13.
11 September 2007, 1:53 amIf you then want the list back in A1:A13 copy B1:B13 and Paste Special Values.
Liz:
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.
11 September 2007, 2:23 amAndy Pope:
Liz, if you want you can email your workbook and I will take a look see.
andy AT andypope DOT info
11 September 2007, 3:10 amLiz:
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
11 September 2007, 3:27 amLiz
Andy Pope:
glad you're sorted.
11 September 2007, 5:29 amTom Hendrix:
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)
29 February 2008, 5:57 am2. 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.
Anjor Bhaskar:
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,
10 April 2008, 2:19 amDave:
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))
24 April 2008, 1:09 pmDave:
Update to comment above. I used:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," "," "),CHAR(10),"#")," #","#"),"###","##"),"###","##"),"###","##"),"#",CHAR(10))
24 April 2008, 1:29 pmBrad Yundt:
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
25 April 2008, 7:52 pmPaul:
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
4 May 2008, 9:20 pmTushar Mehta:
Paul: Recheck Dick's original post particularly the sentence that starts with "If you get a bunch of squares"
6 May 2008, 6:26 amPaul:
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:
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?
7 May 2008, 8:53 pmPaul:
Never mind, I got it!
If anyone else has this problem...this works:
works great even on the old Excel 2000 (that some of my clients still have).
pretty simple
7 May 2008, 9:12 pm