You’ve probably had to convert text to columns before in Excel. Usually in one cell, you’ll have a long line of text that is separated by commas, semicolons, or some other delimiter and all you’re trying to do is get each value into its own column. Something along the lines of this:
This is probably one of the most common use cases of Text-to-Columns I’ve seen. You have text in the format “Last Name, First Name” and you want to split this into two columns with one column being the First Name and the next column being the Last Name. What happens when you have multiple text in a cell entered in as new lines like this?
This is all in one cell and each text is separated by a new line. In this case, it looks like this was a database dump and all the text is put into one cell and our job is to put each value into a new column. The problem is, there is no delimiter! You could add a comma after each value but that would take forever if you had a cell with say 50 lines of text. How do we solve this?
Convert text to columns when the cell has multiple lines of text. Why is it so hard??
Convert Text to Columns For Cell With Multiple Lines With SUBSTITUTE()
For Windows – ALT + Enter. For Mac – Control + Option + Enter. Here are the steps to start a new line in Excel Cell using the shortcut ALT + ENTER: Double click on the cell where you want to insert the line break (or press F2 key to get into the edit mode). Place the cursor where you want to insert the line break. I didn't mean how to enter a line break in Excel. I mean if you export a CSV with a cell containing line break and then import the file back, Excel cannot handle it correctly. Here is some workaround – Tom Yeh Feb 5 at 2:08.
The simple answer is using the function SUBSTITUTE() . It doesn’t seem intuitive, but what we need to do is format the cell with multiple lines of text so that it’s easy for the Text-to-Columns operation to work. We basically want the text in this cell to look like this:
Notice the commas after every value? Once the text in the cell looks like this, then we are ready to use the Text-to-Columns button to split the text up by the commas that separates each value. The key with using the SUBSTITUTE() function is we want to replace each new line with a comma. The ASCII character code for a new line break is 10 for PCs and 13 for Macs. In Excel, you can use the CHAR() function to represent different ASCII codes so we can do CHAR(10) to represent a line break. So in a a cell next to the cell with all your text, you can write the following formula to replace all the line breaks with a comma:
Let’s discuss what this formula does. All the SUBSTITUTE() function does is replace a character or characters in a text with another character. In this formula, the cell with the data in multiple lines is B2. The second value in the SUBSTITUTE() function is the actual text we are trying to find in the cell. In this case, the “text” we are trying to find is a new line break which is represented by CHAR(10) which we just discussed. Finally, we want to replace all occurrences of the new line break with a comma which is the last value in the SUBSTITUTE() function. If you apply this formula to this text, the result will look like this:
You’ll notice all our text goes onto one line with each value separated by a comma. This is exactly what we want because now we can use the Text-to-Columns operation to split this long text into columns.
Copying Text As Values For Text To Columns
If you try to use the Text-to-Columns operation on the cell where you have the SUBSTITUTE() function you’ll notice you’ll get this in the dialog box:
This isn’t correct because we don’t want to do text to columns on the text of the SUBSTITUTE() function, but rather on the resulting text of the function. All you would need to do at this point is to a Copy and Paste Special Values so that we get just the values from the function rather then the formula. After you do the Copy and Paste Special as Values, make sure you selected “Delimited” in Step 1 of the Text-to-Columns dialog box. Then you want to select Commas in Step 2:
Enter Line Break In Excel Formula
You’ll see a preview of what the data will look like and the result looks exactly like what we want: the text (separated by commas) is split into multiple columns. The result in Excel should look like this:
Conclusion
How To Enter A Line Break In Excel For Macs
There are multiple ways of solving problems in Excel, and this example shows how you can use different text hacks to get the result you want. This exercise was actually something asked of me in a workshop I gave a few weeks ago, and I didn’t know the answer until after I found that new line breaks are represented by CHAR(10) in Excel. Once I figured this out, I knew using the SUBSTITUTE() function, Paste Special as Values, and the Text-to-Columns would solve the problem of getting the source text into new columns.
Subscribe to Dear Analyst – A podcast about Excel and data analysis
Subscribe: Apple Podcasts | Android | Google Podcasts | Stitcher | TuneIn | Spotify | RSS
How to use shortcut to add new line/line break in a cell in Excel?
In Excel, when you type a long data into a cell, the data will spill over into next blank cells at default. And this tutorial is talking about adding new lines as you need in a cell when you type the long text string.
Add new line in a cell with shortcut
To add new line breaks in a cell as you need in a cell, you just need to remember some hotkeys.
Place the cursor at the place of the cell you want add new line break, then press Alt + Enter keys simultaneously. Then you can see the new line break added into the cell.
Note: If your computer installed with Mac system, you can use the shortcut Control + Command + Return keys.
Relative Articles:
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
How To Enter A Line Break In Excel For Mac Shortcut
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
or post as a guest, but your post won't be published automatically.
How To Put A Line Break In Excel Mac
Loading comment... The comment will be refreshed after 00:00.