How to Split Multiple Lines in a Cell into a Separate Cells/Columns
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 - trumpexcel.com/learn-excel/
Paid Online Training - trumpexcel.com/excel-training/
Best Excel Books: trumpexcel.com/best-excel-books/
⚙️ Gear I Recommend:
Camera - amzn.to/3bmHko7
Screen Recorder - techsmith.z6rjha.net/26D9Q
USB Mic - amzn.to/2uzhVHd
Wireless Mic: amzn.to/3blQ8uk
Lighting - amzn.to/2uxOxRv
Subscribe to get awesome Excel Tips every week: kzread.info...
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial
Пікірлер: 100
You are a certified genius!!!! I knew there was a way to insert a delimiter but couldn't find it. Two days working on this...THANK YOU THANK YOU THANK YOU!!!
@trumpexcel
Жыл бұрын
Glad the video helped 🙂
I searched for a while to find this. Thank you!
Thank you , i wasted hours trying to figure out ,Ctrl+j was truly magical part for me
Such a fast and simple explanation. Thank you so much! I have hundreds (maybe thousands) of those cells and your video helped me to save huge time and energy!
Amazing to find this, perfect solution. Brilliant. Thank you.
Such I searching from last two days Great thumps up
You are amazing and a life saver. I was looking for this command for 3 days.
Thank you, this was very helpful! I appreciate you making this video. I've been manually doing this for months! Amazing!
Explained in short and sweet manner!! Very useful.. Good Job!
This looks simple but saved me a hell of a lot of time. Thank you !!
Extremely useful. Thank you.
Excellent! Thank you sooo much for this useful information.
It works and it's really amazing, Thanks for sharing!
Thank you brother, solved a big problem flor me today. Big relief! Dhanyavad
Extremely helpful! Thanks !
great info....this just saved me!
Good video, saved me loads of time,! Thanks
Fantastic! You are the best!
Fantastic. I was struggling with this data and this video helped me with thr right representation. Thank you.
Very useful post.keep sharing. Thanks.
Thank you very much!
omg. that was amazing! thank you!
This helped me. Thank you so much! :)
That was life saving tip..!! Thanks a ton.
Is there a different key command for this on a mac?
Great tutorial. Thanks.
This is super helpful!!! Thank you for sharing!
I could not agree more. You are awesome sommit Bunsel. .
amazing tip you made my day
Great hack! Really helpful. thank you
Sir you are amazing....keep teaching sir
Thank U so much . You helped me saving a lot of work. Here you got my LIKE
Thank you chief ❤
Amazing solution
Thanks you so much.
your channel worth gold, please do not stop! :)
Thank you so much.
Thank you!
Thank you so much bro ..... Thanks lot bro ...
Thanks helpful and useful
Bro Thank you so much. This trick helps me a lot in my situation.. I am really Thankful to you for this video..
Thanks a lot!
Good video.... Donno if i could ever have learned it without this video..
@harriedblues8323
6 жыл бұрын
Same with me... I have tried lot of text (write-ups) and spents days but couldnt figure it out until i watched this tutorial...In a min, i was done. Awesome!!
Nice Tutorial.
You rock man!
Big Thumb up. Thanks
very useful tip...made my task easier
Very helpful
TY !
Wow..thank you
How to do the same creating another line for words separated by a comma ?
Genius!
For Mac: =SUBSTITUTE(B2,CHAR(10),"\") copy + paste as values where it says "Other:" type "\"
@jessicaleach2145
5 жыл бұрын
When I do this it appears in the cell as though it's fixed, but when I try to split text to columns, it's still just showing the first line. Any suggestions on what I need to do next?
@tanbirrajhossain5046
4 жыл бұрын
Life saver. Thanks a lot. Little detailed step: 1. In a different cell type: =SUBSTITUTE(B2,CHAR(10),"\") 2. Copy the content and paste as values only 3. Click Text to column 4. Uncheck all and type "\" in Other 5. Finish
@luisa.machado6595
4 жыл бұрын
This is genius! Just to clarify, use the steps in this video, but first set up a separate column, preferably to the right and beside your data. Enter Kimberlea's formula in each cell in that column, referencing the cell on your left (instead of the cell B2 in that formula). Instead of separate lines, your data with be in one line, delimited by backslashes. Copy your column with this formula and paste to itself as values. Do the Text to Column as directed. Use the backslash instead of Control- J. Then your data will be split into cells as shown.
@SIMRANKAUR-eg7nc
3 жыл бұрын
U can check a video on "off to office" if u r still not satisfied. I got a video dedicated on text to column.
@rahmatchan5498
3 жыл бұрын
Genius thanks a lot you are my lifesaver
Sumit Sir, Got it ! Thanks. ; )
Thanks a lot. The Ctrl+J is a kind of undocumented magic :) Instead of insertion linebreak character...
always great Sumit 😉
Got the part of using Ctrl J, but I'm using a Mac, tried control J and command J, but neither work. Any ideas?
@rahmatchan5498
3 жыл бұрын
same issue still not working
You're de best!
@trumpexcel
4 жыл бұрын
Glad you found the video useful!
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!?
thanks
How do make it like it was before I used ctrl J? It is not letting me type anything in the "Other" box now.
Can u help me out...I want one sr no and in that I want two or more rows
it doesn't work on macbook. can anyone help please ?
How I can make in other sheet
Kindly help me it does not work in my excel sheet?? 2016!!!
Crtl + J not working in my end . have any other way ? if you tell me will happy . thanks advance
@penguin0075
5 жыл бұрын
Im in the same boat now! Someone sent me a csv with all the address fields in one cell and CTRL J does not work. Did you ever find a solution? Any help gladly apprecceiated
@josef1045
5 жыл бұрын
@@penguin0075 Saving the CSV as Excel and then closing and opening the Excel file worked for me
Is this possible with vba ?
Sumit Sir, Can you do more on videos related to data modelling or arranging. That would be very useful. ; )
Awesome. For some reason Ctrl+J isn't working for me. I am using MS Office 365 with a MacBook Air. Suggestions?
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?
How to put them into rows...
What if I wanna use (;)?
Hey, what are you talking about, just split the cell inside the cell,
@adityalamghare227
4 жыл бұрын
How
❤️
Tell me how to split a cell into rows
Is there any method to reverse this process??
@dawoodkhawaja2892
3 жыл бұрын
Please tell i need this
How to split in and out time in a cell
Hi Ctrl + J is not working for me
@Pavlyuchok13
2 жыл бұрын
Me too
Any one can help me please???
Hello help me please??
Wonderful, thank you! Go TRUMP!!
sir please hindi Mai banaiye please