How to Split Multiple Lines in a Cell into a Separate Cells/Columns

By
admin
-
45
57



In Excel, you can use the Text to Columns functionality to split the content of a cell into multiple cells.

You can specify the delimiter (such as a space, comma, or tab) and the Text to Columns functionality would use this delimiter to split the content of the cells.

Examples of this include splitting first and last names, or username and domain name in email ids.

However, if you have a dataset where the delimiter is a line break (in the same cell), it gets tricky to split these multiple lines in the same cell into separate cells/columns.

In this video, I will show you how to make line break a delimiter in the Text to Columns functionality so that you can use line break to split the contents of a cell.

Here are the steps:
— Select the entire dataset that you want to split.
— Go to the Data tab.Data
— In the Data Tools group, click on the Text to Columns option.
— In the Text to Columns dialog box, in Step 1 of 3, select Delimited and click ‘Next’.
— In Step 2 of 3, uncheck any existing Delimiters selection, and select the ‘Other’ option. Now Use the keyboard shortcut Control + J (hold the ‘Control’ key and then press the ‘J’ key). You will not see anything in the box except a tiny blinking dot (if you look hard). Also, you will see the expected result in the ‘Data preview’ section (as shown below). Click on Next.
— In Step 3 of 3, change the ‘Destination’ cell to the one where you want the output.
— Click on Finish.

Free Excel Course –
Paid Online Training –
Best Excel Books:

⚙️ Gear I Recommend:
Camera –
Screen Recorder – techsmith.z6rjha.net/26D9Q
USB Mic –
Wireless Mic:
Lighting –

Subscribe to get awesome Excel Tips every week:

Note: Some of these links here are affiliate links!

#Excel #ExcelTips #ExcelTutorial

Nguồn:https://dantrionline.com.vn/

Xem thêm Bài Viết:


https://danhnhan.vn/lam-dep

45 COMMENTS

  1. Got the part of using Ctrl J, but I'm using a Mac, tried control J and command J, but neither work. Any ideas?

  2. Thank you, this was very helpful! I appreciate you making this video. I've been manually doing this for months! Amazing!

  22. This isn't working for me (it has before as I've used this video – it's great). Someone sent me a CSV file and the address information for each client is all in one cell as it looks in this video, but it won't add the line breaks when I go through and follow this guide. Is it something to do with the fact it came to me as a csv? HELP ME PLEASE!?

  40. Hi, Bansal. Very interesting video, as always.  But how can we do the opposite action. Having datas displayed in different cells to be regrouped into one cell, separated by "-"? Thank you in advance for the explanations you may bring us. Cheers and all the best to Trump Excel.

  43. Good information.  However, in the example the cells contained the exact information.  (i.e. name, address, city, country)  If the cell only contains name, address & city, how do perform the same operation?

LEAVE A REPLY

Please enter your comment!
Please enter your name here