Split Multiple CSV or Line Break Values from One Cell to Multiple Columns or Rows

No video

Split Multiple CSV or Line Break Values from One Cell to Multiple Columns or Rows

Рет қаралды 84,718

Күн бұрын

Have you ever gotten a file of serial number (or other list type of values) that were combined into one cell? What you really wanted was to have this listed out separately. Sometimes you were lucky that they were separated (or delimited) by commas or other common separators like semicolons. However if it was separated from some unknown character like a line break (paragraph break or line feed) that would be a bit more tricky...or not. This video show how to pull out those values and have the output in separate columns or rows. Check it out.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain...
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoff...
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

Пікірлер: 75

@davidwebster1277 2 жыл бұрын

Thank you, thank you, thank you! Just spent 3hours searching for the information you have so clearly and concisely delivered.

@DougHExcel 2 жыл бұрын

Glad it was helpful!

@mohamedabdo-xg6tw 2 жыл бұрын

just WOW! I have tried 8 different ways in Python and R and I couldn't get it. Thank you so much. I will definitely support your channel by buying you a coffee.

@DougHExcel 2 жыл бұрын

Thanks so much for the support!!

@katefonzi3669 2 жыл бұрын

I have been trying to solve this problem for years! I needed something that someone with okay skills in Excel could replicate (with step-by-step instructions). Thank you so much, this is awesome!

@DougHExcel 2 жыл бұрын

You’re welcome, feel free to share & spread the knowledge!

@blakewood4756 3 жыл бұрын

This was a life saver for something I had to do for work. Big thanks!

@DougHExcel 3 жыл бұрын

Glad it helped!

@caithenry8429 Жыл бұрын

God bless you for this! This just saved me SO much time

@DougHExcel Жыл бұрын

Thanks Cait Henry, glad it helped!

@folorunsolala9672 Жыл бұрын

Thank you Thank you and Thank you. This is what i have been searching for. You made my day splendid

@DougHExcel Жыл бұрын

Glad it helped!

@JavierMadridC Жыл бұрын

This guy really deserves so much thumbs up!

@DougHExcel Жыл бұрын

Hi Javier Madrid, thanks for the kind words!

@user-ij2pi8se3x Жыл бұрын

truly straight forward and very clear now it is easy

@DougHExcel Жыл бұрын

Hi iTLeiffLotSix TadweerAlphamed, thanks for the comment!

@spencerpitcher8441 2 жыл бұрын

Is there a way to make this a dynamic function. Where when the parent spreadsheet is updated, the new table automatically updates?

@sureshsawant1897 3 жыл бұрын

When I m trying to solve ,(convert) line break data into multiple column numbers which seperated thru spaces are splitted but numbers which have last numer 00 not showing proper result as it display in line break data .....kindly advise what to do? Is is possible to send that example to u so that u can suggest better way to solve it?

@ariii6447 2 жыл бұрын

you are the best, thank you!!

@DougHExcel 2 жыл бұрын

Hi Ariii, thanks for the kind words!

@alekm9446 3 жыл бұрын

Excellent explanations...

@DougHExcel 3 жыл бұрын

Glad you liked it

@lukeh2440 3 жыл бұрын

If I could leave a million likes I would!!

@DougHExcel 3 жыл бұрын

Hi Luke Hermes, thanks for the kind words!

@johnzehrbach820 3 жыл бұрын

My work life it now complete!

@DougHExcel 3 жыл бұрын

Hi john zehrbach, glad to hear 😁 thanks for the comment!

4 жыл бұрын

Hello. Very nice video, thank you. What if I have a lot of text in the cell and my separator is a blank line? I can't get it to work :(

@DougHExcel 4 жыл бұрын

The line break method should have applied maybe I'm not understanding the question...

@mohammadkashkooly1987 Жыл бұрын

Thank you so much. I have dates separated by space then (e.g.10/6/22 10:45 [Level 3]) how do i do this please? Bunch of these some included text in other columns? Thank you

@DougHExcel Жыл бұрын

if it's textual and there's some pattern try kzfaq.info/get/bejne/qp54o7dqtZPWmZs.html

@ExceliAdam 6 жыл бұрын

Why didn't you split by rows in Power Query? You have this option and you don't need to do unpivot columns.

@DougHExcel 6 жыл бұрын

Excellent option...thanks for adding to the thread!

@roshanrewane7125 4 жыл бұрын

Hello I tried this option but it’s not working if We have () brackets in between, example 6441BY(6441BW) 6441BW(6441BY) In single cell and if I want to split that, please help me it’s urgent

@vaibhavsingh-dg6gm 4 жыл бұрын

Thankyou so much, very helpful

@DougHExcel 4 жыл бұрын

Glad it was helpful!

@abuzarchowdhury5089 5 жыл бұрын

Hi Douh, this is very useful video. Thanks for sharing with all. i have qus for you. i have data list and in a row with multiple lines and i want it to split in different columns. how would i do?

@DougHExcel 5 жыл бұрын

if that video didn't work out, maybe some of the other one might give insight kzfaq.infosearch?view_as=subscriber&query=row+split+column

@winstonarockiaraj4736 4 жыл бұрын

Is there a way to split the quantity by the number of rows? say I have 20 as the quantity and it gets split into 4 rows, then i would like to see the quantity as "5" for each row... the value in quantity column should be divided by the number of rows for each item grouping, is that possible?

@DougHExcel 4 жыл бұрын

Maybe, I think this would involved numbering the rows, a modulo function to have a pattern to group into 4s and then grouping.

@vincentgnapi7182 5 жыл бұрын

Took a couple replays to finally get it right but Thank you

@DougHExcel 5 жыл бұрын

You're Welcome!

@Luciano_mp 6 жыл бұрын

É de grande ajuda. Obrigado Doug.

@DougHExcel 6 жыл бұрын

De nada! Obrigado!

@chesterumali9226 4 жыл бұрын

Ctrl + J isn't working. How do I delimiter if the values are separated by a new line? like 3 lines within a cell. Normally ctrl J would work in the normal text to column but not in power query. Thanks!

@DougHExcel 4 жыл бұрын

should work...try Ctrl+J in word and copy it over could be a test...

@mohamedchakroun4973 6 жыл бұрын

Thank you for the ctrl j refresh, i forgot it :-)

@DougHExcel 6 жыл бұрын

Hi Mohamed Chakroun, glad you liked it, thanks for commenting!

@abhijeetpatre3327 5 жыл бұрын

Thank you so much man!

@DougHExcel 5 жыл бұрын

You're welcome!

@sevainternet1383 3 жыл бұрын

very useful

@DougHExcel 3 жыл бұрын

Glad you think so!

@ksawesome15 3 жыл бұрын

What if I'm trying to have multiple separate rows into one single cell? Like I still want the rows to be separate so its tiny cells into one big cell?

@DougHExcel 3 жыл бұрын

Maybe this vid will help kzfaq.info/get/bejne/eN52ocRkl7-rfok.html and use the Split using special characters option.

@bodavinay4420 4 жыл бұрын

You are amazing.!!

@DougHExcel 4 жыл бұрын

Wow, thank you!

@Meolimo 6 жыл бұрын

Nice Doug thanks !

@DougHExcel 6 жыл бұрын

Hi Mario Limoges, thanks for the comment!

@Meolimo 6 жыл бұрын

c'est moi qui te remerci :-)

@vietanh-sungroup8048 4 жыл бұрын

thanks! Sir

@DougHExcel 4 жыл бұрын

Most welcome!

@bernardblumberg803 6 жыл бұрын

Great Stuff.,

@DougHExcel 6 жыл бұрын

Thanks Bernard Blumberg!

@yotarius3417 4 жыл бұрын

it is ctrl+j for people who don't wanna sit here for 10 minutes

@DougHExcel 4 жыл бұрын

Hi eli clay, thanks for adding to the thread!

@jasonv7228 4 жыл бұрын

what would you put in for a mac?

@DougHExcel 4 жыл бұрын

Depends on what version , the PQ features recently became available for MAC and though most of the features have parity with the Windows version, they commands may be in different places...so you'd have to look for them

@EngineeringTR 3 жыл бұрын

let's say: from 0001 to 1000 in different cells suhc as (A1=0001,A2=0002,... A999=0999, A1000=1000) --> how is it made that these will only be in one cell as: 0001,0002,0003,...,0999,1000

@DougHExcel 3 жыл бұрын

maybe one of these kzfaq.infosearch?query=textjoin