System: Mac OS X Yosemite; Office for Mac Home & Student 2011: I am concatenating Hebrew text and placing in cells in a column in an Excel 2011 worksheet. The text will NOT display properly unless the column width is increased enough so there is only one line. In this MS Excel tutorial from ExcelIsFun, the 228th installment in their series of digital spreadsheet magic tricks, you'll learn how to use the CHAR (10) function to add hard line returns (forced wrapped text) to a text formula created with the ampersand (&) and concatenation tools. Inserting a line break in Excel is quite easy: Just press Alt + Enter to add a line break inside a cell. This keyboard shortcut works the same way on Windows and the Office 2016 for Mac. This keyboard shortcut works the same way on Windows and the Office 2016 for Mac.
Activeyesterday
When I type a long sentence (or more than one sentence) into more than one cell in a row and apply the Text Wrap option to these cells, the cells have an extra blank line on the bottom of them. There are no extra spaces after my text. I have put the cursor at the end of the text and used the Delete key.
My text is formatted to be aligned to the Top and Left.
Is there some other setting I can use to get rid of this extra blank line? I realize that Excel isn't really made for word processing. But, I need to enter in comments on this sheet and use the Text Wrap feature.
I am using Office/Excel 2007 for Windows.
kenorb
12.9k1818 gold badges8888 silver badges132132 bronze badges
JuncoJunco
15 Answers
I was able to reproduce the behavior you are seeing (also in Excel 2007). I can't find a way to get rid of the extra space once it's there, but I found that if you reverse your steps and apply the Text Wrap in a cell prior to entering your text, you will not get the extra blank line.
bmshortbmshort
I had the same problem just before I've typed this. And here is what I've found.
https://keenfilm.weebly.com/how-to-get-text-direction-on-ord-for-mac.html. When you press wrap text the cells height is automatically adjusted. Also its height is not calculated as we expected. If there is very little space left on the last row of the text, Excel decides that the cell needs one more row.. even if there is not a single letter on it.
If you get the cell's width a little wider, the automatic cell height adjustment removes the extra line.
Nathan.Eilisha Shiraini
2,48811 gold badge1010 silver badges2323 bronze badges
Rumen DimitrovRumen Dimitrov
I found that if you change the width of the column that contains the cell with the extra blank line, then auto sizing the height of the row will usually get rid of the extra blank line. Apparently word wrap messes up with particular cell values and particular widths. Try different widths until auto-sizing the height gets rid of the blank line.
You can widen the column by clicking and dragging the divider between the two column headers at the top of the worksheet. Once the column is wider, auto size the row by double-clicking the divider between the row headers on the left side of the worksheet.
Of course, if your worksheet has hundreds of rows, then each cell in that column has a certain chance of hitting the magic spot, and adding the extra line. Changing the width will clean up some cells, and add unnecessary blanks to others. In this case, the only solution that I have found is to manually wrap the text to my chosen width by inserting extra carriage returns in the middle of the text at the position that I'd like the text to wrap using alt+enter. I then make sure the column is a little wider than my chosen text width, and I won't get any extra blank lines. A terrible and labor intensive solution, but it does work.
Michael KrebsMichael Krebs
Excel lays out text differently in wrapped cells to the way it lays it out in text boxes.
(Try it: create a text box the same width as your wrapped cell, with zero margins, check Resize shape to fit text and Wrap text in shape, and paste in a copy of the text from your wrapped cell).
Knowing this, it seems that the problem occurs because the Autofit Row Height command adjusts the height of the row as if every cell of the row is laid out using the algorithm used for text boxes rather than the algorithm for wrapped cells. Autofit Row Height also ignores merged cells, so it really isn't very usable in many situations.
Solution: put your text into text boxes rather than wrapped cells. Of course, this introduces its own world of pain, but if you generate and populate the text boxes using VBA it isn't too difficult. https://keenfilm.weebly.com/no-small-text-for-mac-thunderbird.html.
How To Wrap Text In Excel
Andrew PartridgeAndrew Partridge
Moving to new sheet worked for me on Excel 2016:
SeyfSVSeyfSV
I tried 6 of the 7 answers that were here (excepted the text boxing) and with a bit of fiddling the ideas all worked but, on some rows only. I'm working on an Excel 97-2003 (.xls) workbook using compatibility mode from Excel 2016 but I see 2007 has the same commands I used below..
Another thing that worked on some was to type a blank into the empty cells between the other data. Some rows were corrected by copying the formatting from a good example row, onto the problem row. Using the formatting dialog box to change all the formatting on all the data at once and reapply it also worked on a few rows. What I found that worked on all the many misbehaving rows I had left at once was to select all the data area (CTRL+HOME then CTRL+SHIFT+END will select cells that surprisingly contain invisible data too), go to the HOME tab, EDITING section, select the arrow beside CLEAR, choose CLEAR FORMATS. Then select all the data you want text wrapped and either right click or use CTRL1 to open the Formatting dialog box, alignment tab, and checkmark beside WRAP TEXT, ENTER, check the data all over for wasted lines (are there blank spaces leading or following your text?), then reapply any other formatting you were using. Voila! The end of many hours of frustration.(
RickRick
Please do the following:1. Highlight the column, control+h (to find and replace) put a space in find and put any special character to '-' to replace.2. Convert 'text to colomn', delimit with the special character replaced.
Enjoy..
SmruthySmruthy
I read in diagonal some answers and some are useful. Eventually after fidling with the issue I found:
So I messed a bit more and here's the (eventual) solution - in Excel 2013:
Issue should disappear. Formatting text in tables in powerpoint for mac?. Let me know if this fixes the issue.
Ontanoi SavelOntanoi Savel
The only solution for this problem seems to be complete reinstalation of the MS Office. I searched all over the internet forums and nobody knows how to solve it. For me, the reinstalation worked, so give it a try. :)
Vítězslav ČástkaVítězslav Částka
This bug remains in Excel 2016, and it has annoyed me for decades. Making the row fixed height always comes with the risk that some text will get hidden later if more text is added to the cell. Michael Krebs' solution will work most of the time and avoids this risk. Aganju seems to understand the problem well. I think that TaeKwonJoe was just lucky with Excel 2013. Paul Kienitz has my sympathy.The other fix that avoids the risks involved with fixed row heights is to tweak the font size up or down by a small fraction, say from 11point to 10.8point. Like adjusting row widths, it's a 'one time fix' that doesn't guarantee the extra space returning, but this way you don't mess up the formatting of the rest of the sheet. Good luck!
ian campbell-kellyian campbell-kelly
I am on a work computer and don't have administrator rights, so some of these options weren't able to be tried, all others didn't work for me.
I did find a resolution for Excel 2016. I simply used the format painter from a cell that didn't have the spacing issue and copied the formatting to the offending cell. It worked on all of my cells and was minimal extra work, not a great fix, but it can keep from having to adjust fonts or cell sizes and messing up the other formatting.
JacobJacob
Jacob is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
This problem was bothering me for a very long time too.
My only solution to get rid of the extra space /enter was to finish the sentence with a dot / full stop / the key '.' This works 100% for me, where all other solutions failed (including copy pasting in notepad (back and forth), tinkering with layout, cell width, etc)
Yes, turns out it was that simple. Just finish the line with a hard break / dot.
Good luck!
RonnieNRonnieN
i faced same problem & now solved it. Pls select the cell, click F2, bring mouse pointer at end of the text (press end button), click delete button couple times till u feel that some unwanted blank space is removed, then press enter button.. here u go. Thanks.
HoqueHoque
How To Wrap Text In Indesign
I highlighted the entire row. Then clicked on 'Home' at the top left. Then a 'Format' drop-down menu appeared on the upper right. With the row still highlighted, select 'AutoFit Row Height' from the 'Format' drop-down. Magic!
Joseph A SilvaJoseph A Silva
How To Wrap Text In Google Slides
When you are on a cell, if you click the 'normal style' it should get rid of any extra space in the cell.
user685183user685183
Not the answer you're looking for? Browse other questions tagged microsoft-excelmicrosoft-excel-2007 or ask your own question.Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |