So, the above formula finds all the line breaks and replace these with blank – which essentially means that these line breaks have been removed. The SUBSTITUTE function finds and replaces the CHAR(10) character – which represents the line feed character. Suppose you have the dataset as shown below and you want to remove the line breaks from all the cells.īelow is the formula that will do this: =SUBSTITUTE(A2,CHAR(10),"")
Remove blank lines in excel for mac update#
While the Find & Replace method gives you static results, the formula will give you results that will automatically update in case you make any changes in the original dataset. Remove Line Breaks Using FormulaĪnother way to get rid of line breaks in Excel is by using a formula. Note: It’s recommended to create a backup copy of the file that has the data so that you don’t lose it in case you need it in the future. In case you want to remove the line break and want to replace it with something else instead of the comma, then you need to specify that in Step 6 (in the ‘Replace with’ field) This will give you the result in a single line in a cell in Excel. The above steps would remove all the line breaks and replace these with a comma.