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:
Screen Recorder – techsmith.z6rjha.net/26D9Q
USB Mic –
Subscribe to get awesome Excel Tips every week:
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial
Xem thêm Bài Viết:
- Oripan Gold Soap – Bánh rửa mặt trị mụn số 1 Hàn Quốc – Hotline: 19002236
- TOP 5 SẢN PHẨM TRỊ MỤN LƯNG HIỆU QUẢ | BODY CARE ROUTINE | LÂM À
- Hướng dẫn kẻ mắt cho người mới bắt đầu – EYELINER TUTORIAL FOR BEGINNERS |Ty Lê|
- Điều Chị Em Cần Nhớ Khi Uống Thuốc Tránh Thai Để Trị Mụn Trứng Cá
- Góc chia sẻ: 4 Cách gỡ bỏ tận gốc autocad một cách triệt để
Got the part of using Ctrl J, but I'm using a Mac, tried control J and command J, but neither work. Any ideas?
Thank you, this was very helpful! I appreciate you making this video. I've been manually doing this for months! Amazing!
It works and it's really amazing, Thanks for sharing!
Great tutorial. Thanks.
Extremely helpful! Thanks !
Awesome. For some reason Ctrl+J isn't working for me. I am using MS Office 365 with a MacBook Air. Suggestions?
Explained in short and sweet manner!! Very useful.. Good Job!
Can you do more on videos related to data modelling or arranging. That would be very useful. ; )
Got it ! Thanks. ; )
What if I wanna use (;)?
Amazing… Save lot of time…
Bundles of thankssssssssssssssssssssssssssssssssssssssssssssss
copy + paste as values
where it says "Other:" type ""
<3 Thank you!
amazing tip you made my day
it doesn't work on macbook. can anyone help please ?
How to split in and out time in a cell
This helped me. Thank you so much! 🙂
Is there a different key command for this on a mac?
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!?
omg. that was amazing! thank you!
Thanks you so much.
Good video, saved me loads of time,! Thanks
How I can make in other sheet
Excellent! Thank you sooo much for this useful information.
Thank you so much bro ….. Thanks lot bro …
How to do the same creating another line for words separated by a comma ?
How to put them into rows…
great info….this just saved me!
Amazing to find this, perfect solution. Brilliant. Thank you.
Crtl + J not working in my end . have any other way ? if you tell me will happy . thanks advance
How does that function CTRL + J would look like on excel on mac?
can u help me with this? example X-40.666Y-46.805Z25.1 . can u separate the x,y and z components of a massive data?
Fantastic! You are the best!
Thank you very much!
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.
I could not agree more. You are awesome sommit Bunsel. .
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?
Very useful post.keep sharing.
your channel worth gold, please do not stop! 🙂