Split Multiple Lines in a Cell into Separate Cells - Rows or Columns. Text to Columns & Power Query
Тәжірибелік нұсқаулар және стиль
Download the feature file here: www.bluepecantraining.com/exc...
------------------------------------------------------------
Blue Pecan Computer Training Ltd offers on-site or online Excel training for businesses. Training can be delivered at your business premises in the south-east of England or via Teams wherever you are in the world. Visit our training page at www.bluepecan.co.uk
In this video I demonstrate how to split multiple lines within a cell in your Excel worksheet into multiple cells. The data can be split into rows or columns. There are two methods for splitting into columns: Text to Columns and Power Query. For splitting into rows you have to use Power Query.
If your version of Excel doesn't come pre-installed with Power Query, you can download it here:www.microsoft.com/en-gb/downl...
------------------------
Пікірлер: 109
Thank you so much for outlining these techniques so clearly - this has saved me a TON of time on 20+ sheets
@tinygoblin
2 жыл бұрын
@Chester Tugwell I'm running into an issue with one sheet - the error is "We cannot convert the value 1 to type Text" -- all of the other sheets I've used Power Query on have not had this issue. Is this a cell formatting error?
This just saved me hours of manual copying and pasting. Thank you so much!
Thank you so much. That was so helpful I knew the text to column method but have been searching the method to get in to rows!! Crisp and clear , spot on👍🏻👍🏻
@pietroxor1745
Жыл бұрын
i have a same problem, i must save a file in cvs with the text in a single row...
Finally I found this tutorial after wasted a lot of time watching other videos “text to columns”, only your tutorial gave me exact the solution I needed for my problem. Thanks a lot!
@victorogunsanya
Ай бұрын
2 years later, and this video is still the go to.
THANK YOU SOOO SO MUCH!!! I am new to excel and learning how to use vba and macros but I had a list of customers that QB exported and they formatted it in a bad way and had all the address on different lines in each cell. I was banging my head for hours and after this, I was relieved in 2 min
Sir, You are brilliant! If I screenshotted the table sent to me, you would laugh. You saved me a tremendous amount of time I do not have. Thank you plenty. I look forward to future instructional videos.
@ChesterTugwell
2 жыл бұрын
Carmelo - glad to have saved you time. That's the idea behind these videos!!
Really useful video, had been searching for a solution to this for a while...thank you!😄
Thank you for this video, it saved me a ton of time!
This saved me a lot of time. Thank you so much! 😀
omg god - this tool is utterly freaking amazing. You have literally saved years of my life. like its the difference between BC and AD One moment i was aimlessly wandering the wastelands of manually cut and pasting rows. I was dammed, broken and doomed to manually create row after row, several hundred thousand of them. And then the next moment your enlightenment shone upon me imbuing me, embiggening me with your intelligence, spark and knowledge. For once I was lost and now I have power query! bravo. can i pay you money, or with even souls or blood? What will you have me do my liege!? edit: subscribing & liking!
@ChesterTugwell
2 жыл бұрын
Well I don't want you to wander the wastelands any more Chug.
Thank you so much for this guide. This saved me a ton of work.
This helps a lot. I have a follow-up question. How can we make this work if there are line breaks on multiple columns to rows? is there a way to break it up so every line break within a row gets split to different rows?
Brilliant technique, thank you for sharing.
Good page, helped save 18 hours
Nicely done... Thanks Mr T 🙏👍
Massive time saver, thank you
Thanks for the instruction on column B text split into different rows. However, I have more than 200 columns same as your column B data format. Do I have to repeat your steps on each column to split every column data the same way as your example video?
Thank you so much for this video.. Saved a tone of my time!!
Thanks! Any idea how to create duplicated lines based on cell value. For example have in cell 5 and power query create same line 5 times?
Thank you so much , this is really really helpful. :)
Excellent! Thank you!
Thank you so much for saving hours of my work day
Great, thank you! Please could you tell us if there any formula exist to split multiple lines in separate row|columns wiht cntr+j delimiter? I need to create a template which does it automatically without going for text-to-columns in panel.
Lovely, you made my day! Thanks!
Thank you so much. This is i'm looking for.
Thank you so much, very HELPFUL!
Its was very helpful.. Thanks a lot :)
Thank you so much for this incredible help.
Great support, thanks a million
This was a lifesaver!!
Thank you, This was very helpful!
Thank you , you made my job easy :-)
Thank you! I was doing this manually at work but not anymore.
Brilliant tutorial, this was really helpful. Thanks for the effort.
@ChesterTugwell
Ай бұрын
You're very welcome!
Thank you so much. Was very useful to us.
Thank you Chester!!!
Thanks for sharing, very useful
Wow! Thank you for this!
thanks for the power query tips....it works for me.
save a ton a time. Many Thanks!!
Hi Chester great explanation! can you help with a problem? I have a row of data from A2 to Q2 of which I would like in cell R2 but with line breaks to form a list, so I can copy and paste into a QR code add in so it appears in my QR code as a list I have tried CHAR but is a long winded process.
Thank you for sharing; is is very helpful
Very helpful! Saved a ton of time.😀
Thank you!! This is very helpful
Great stuff, much appreciated
THANK You sir from Bangladesh
Hi Chester.. Is there a way where the first column doesn't copy itself below??like Trn1 in your video should be available in 1st row and in the 2nd it should be blank.. Any suggestions please..
Thanks a lot, Sir!
Thank you!! so helpful!
This video is very helpful. I have a more complex scenario, where there are two columns with multiple lines, delimited by line break. There are other columns with single values that are common for all lines in these two columns. Is there a way to split these two columns into multiple rows, with other common values copied over to these new split rows? e.g.: Input data: COL1 COL2 COL3 COL4 Dogs Pets Beagle Patchy Bulldog Brown Terrier Beige Cats Wild Marbled Cat Spotted Bay Cat Brown would like the output to be: COL1 COL2 COL3 COL4 Dogs Pets Beagle Patchy Dogs Pets Bulldog Brown Dogs Pets Terrier Beige Cats Wild Marbled Cat Spotted Cats Wild Bay Cat Brown Is this possible?
You saved my day !!!!
This really helped me figure out something that was killing me. I'm still hung up on one thing I couldn't figure out with this tutorial. I'm trying to feed numeric entries from automated bank entries on one page, into a column on another. It's also important that every time bank info is fetched, the respective transactions go into the subsequent cells of their corresponding columns. Any ideas?! :)
Thank you so much!!
super .......😃 thank you for sharing @Chester Tugwell
Wow thank you!!!
I was trying to figure out how to do this in VBA but then found your video.
Super, Merci beaucoup
it helped me..thanks a lot
Wonderful , thanks alot
Thank you
Thank you so much for an informative video, I need a help plz. I have 4 column's like your column B. can you please help me with this issue.
Thank you!!
excellent
Thanks for the solution it works 👍
@ChesterTugwell
Жыл бұрын
Happy to help
Is it possible to go the other way around also? - Move data in the columns back to a single cell?
Very useful! The volume is a little low though.
I cannot find the Power Query tools anywhere and I am using the latest version of Excel on a Mac. Can someone point me in the right direction? I need to split one cell's data by comma into the same column, but in multiple vertical rows. Any help greatly appreciated.
Awesome!
I’ve got 3 Columns, column 1 being a part number and columns 2 and 3 being a quantity and a price. The part number is column 1 is duplicated down for every quantity and price. Is there anyway to get columns 2 and 3 to convert to a single row? Hard to explain without a picture to explain. Thanks.
how can I use this on Mac?
Thats nice, but shouldnt it update automatically, at least in 365 versions of online files and desktops? Or is it possible to setup somehow?
Anyone know the way to do this on Mac where Ctrl+J isn't available?
Very helpful video
@ChesterTugwell
Жыл бұрын
Glad it was helpful!
Best and easy , thanks
@ChesterTugwell
Жыл бұрын
Most welcome 😊
Is there a way to do this on Mac? Ctrl+J doesn’t seem to work
How do you use the ctl+J function on a macbook
Is there a method to do the opposite? I have multiple lines I want to merge into one cell. Basically taking the beginning shot (0:10) you had of the bottom right snapshot on the right and turning it into column B.
Hello, are there other ways of doing it automatically other than PowerQuery? I have 8 line 8 times on a single row, so this would create at least 64 new columns if I want to process that, is there a cleaner way to lets say process entire information of each row within single cell to outcome into single cell? I fear even if I use PowerQuery it will crash after 1000 rows of input...🙃
Thanks!
@ChesterTugwell
2 жыл бұрын
Many thanks
Super thanks
Very usefull
This unfortunately doesn't work for cells with lots of text in it. If there is lots of text in a cell you have to double click the cell first for the Text to Column feature to work. I have a cell with over 4k characters and it doesn't work. If the cell has only a few lines then this method works.
very good.
I have Excel 2003 and didn't have these options. I just pasted the data into Word, then copied it back to Excel and it was in rows. Every other line was blank and I had to sort those out and delete them.
is it possible to make it with multiple columns that have multiple rows in one cell ?
Nice, atleast I have an idea now
I love u!
This Option now show in Excel 2013
How do we do this on google sheet please?
What if there is no coma
i want opposite of this
what about there are no patterns...
control+J just keeps giving me the error sound :(
Doesn’t work on a Mac to split into rows
I want to split address of 1000 of people maximum length is 200 without using split function
Thank you @chester tugwell
Thank you!!
Thanks!
@ChesterTugwell
Жыл бұрын
No problem!