How to Split and Transform Text in Excel (Unbelievable Power Query Tricks)

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/transform-text-file

[@elyorkeldiyorov3641]

HI, I was just wondering Why I am not havin several options in my SPLIT COLOUMN function in Power Query. I use Office 2019 Thank you beforehand :)

[@suzysasse]

Thanks!

[@LeilaGharani]

Thank you!

[@peteringvorsen890]

You make the difficult things in Excel easy to understand and you always present it in a professional way 👍 So it's always magical when watching your Excel videos, Leila 😊

[@LeilaGharani]

So nice of you! Thank you.

[@Deependra1991]

Excellent tips n solutions to avoide lengthy formulas and great level of teaching and presentation. That's why you are the best excel teacher on youtube.

[@LeilaGharani]

So nice of you

[@ΣυμεώνΠαπαδόπουλος-λ4ο]

Power Query is really a revolution. I am so happy that you are creating these courses, you are so professional in everything you do!

[@LeilaGharani]

Wow, thank you!

[@candidorodrigues7541]

i really love YOU... explanation very well explained. not very fast and step by step. a few years ago I wasted so much time resolving situations like this. And now I see how simple and FAST it can be .. THANK YOU

[@LeilaGharani]

You're welcome!

[@farzanmoha8935]

Hi dear Leila, I already completed the power query course with someone else. BUT the way you teach is so clear and concise. I am so grateful for the lessons that you taught me.

[@LeilaGharani]

You're very welcome!

[@SigmaSKPV]

Your videos are so super easy to follow. In fact, even if the audio is muted, it is easy to follow. I eagerly wait for your videos each week. Thx a lot.

[@LeilaGharani]

Thank you so much 😀

[@Buhassan5656]

Long time back, I was tackling this problem via formula, it took me two days and result in total failure. Never thought it is as easy as this. Thank you very much Liela...

[@LeilaGharani]

Power Query can be your best friend for tasks like this :)

[@rajanghadi2337]

Not sure why it would be difficult using function. Yes, with power query it's most simple, but even with functions it's not difficult (especially this problem) if our logic is clear with functions.

[@ann-pl2st]

Bonjour Leila. Again, thank you for all your excel lessons. I have been following them about a week ago, and have learnt so much. Every day I look up your tutorials and practice them. It's been most educational. Merci beaucoup. Greetings from France.

[@LeilaGharani]

Wonderful Ann! Avec plaisir :)

[@ArianrhodTalon]

Power Query is a game changer. Once I learnt it, I only used formulas for quick and dirty analysis. Anything long term, it's all on Power Query

[@ianodonoghue6011]

Thank you for this great video. Your tutorials have helped me so much in excel and I'm now known as the "excel guy" but it's you who should be getting the praise

[@nessim.liamani]

You are a godsend sister. Thanks a lot 🙏 I'm Nessim from Brussels. Cheers

[@petr6617]

An excellent, thoughtful example, Leila.

[@nadeemmalik8031]

The most beautiful ever. Your lessons are so captivating.

[@LeilaGharani]

Wow, thank you!

[@sachinrv1]

Excel Power Query, one more feather in the cap. I personally like find delimitor from the end. Thanks for the video Leila. Cannot wait to see more videos on Excel Power Query

[@LeilaGharani]

There will be more Sachin :)

[@scottgaines2677]

Another great video! I have little experience with Power Query, but will be jumping after seeing this video. Thank you so much for all the great content you provide!

[@darrylmorgan]

Hi Leila!Power Query And Power BI Make Complex Work/Reporting So Much Easier Especially When You Learn To Use DAX Formulas..Really Great Tutorial Thank You :)

[@LeilaGharani]

Very true 😄 Glad you like the video.

[@carmenzal7214]

Dear Laila, Thank you very much for you knowledge. It is awesome to know more and more what Excel can do...

[@LeilaGharani]

My pleasure!

[@ibrahimnabil78]

You are amazing Leila, You can't imagine how this Video is very important to millions of users .. thanks thanks thanks a lot ..

[@LeilaGharani]

Glad you think so!

[@mattschoular8844]

Thanks Leila. Power Query is fantastic.

[@LeilaGharani]

It really is Matt!

[@mcd22630]

Your timing of this video is the same as your fashion sense, IMPECCABLE! I have a data set similar to the one in the video and these techniques will simplify my task. Btw pink looks good on you.

[@LeilaGharani]

Glad the timing and choice of clothes was appreciated 😊

[@NotInMyRepublic]

One thought: mention which version you're using. Have learned so much from you, cheers!

[@LeilaGharani]

Thanks John. I'll make sure to mention the requirements for the course.

[@mezianeremila454]

Good suggestion...

[@IvanCortinas_ES]

A wonderful explanation. Power Query is highly productive. Thank you for sharing Leila!

[@kolavithonduraski5031]

i dont use power query at all... but i will now use it to formate my imported tables in a proper and usable format. Thanks !

[@aixcheltjarquin3613]

Thank you Leila. I learn so much from you. I was having a hard time trying to extract text from a spreadsheet. Thanks to your video I was able to do it and I learned something new, Power Query, thanks again.

[@LeilaGharani]

Glad it was helpful!

[@kabirsahib1552]

Your are beauty with brain. So easily and effectively teaches the complex problem.

[@LeilaGharani]

😘

[@magazinejeceva]

My first class Power Query. Really enjoyed. Thanks Leila

[@LeilaGharani]

My pleasure!

[@garys2187]

Incredible!! As soon as I finish the Dynamic Array course (which I am enjoying immensely), I will take this course. Thank you so much for sharing and teaching us in such a clear way. You make the difficult easy by carefully explaining why and how.

[@LeilaGharani]

That's great Gary! I'm happy you like the Dynamic Arrays course so far.

[@garys2187]

My wife looked at this last evening and she was equally impressed (she watched the whole thing, which surprised me, but that is how good a teacher you are). She is interested in taking one of your earlier courses and since I am a long way from being a guru I am also interested. Since the earlier courses are from an earlier date, are they still relevant with some of the new Excel updates? We are not advanced, but we also use Excel enough that we are fairly familiar with it. However, sometimes one thinks they know more than they do - would you have a recommendation?

[@LeilaGharani]

I have a little quiz on my website. If you answer a few questions it will recommend a course. In case you're interested here is the link: www.xelplus.com/quiz-which-excel-course-fits-best/

[@johnborg5419]

Thanks Leila. Power Query is so much easier although building the formula is more fun : ) : )

[@LeilaGharani]

Haha! Yes, that can be fun too. It's great to have options :)

[@paolosoloperto4948]

Leila, you videos are very usefull and simply....congratulation

[@LeilaGharani]

Thank you! 😃

[@DBO3671]

You're awesome. Always sharing clever tips in a clear and unambiguous way. A true MVP. Thank you.

[@LeilaGharani]

I appreciate that!

[@nujjigram]

Gosh you are just so amazing. Ive learnt so much from you last 6 months ! Thank you so much for helping everyone. The way you explain is beautiful

[@LeilaGharani]

Wow, thank you!

[@silvanoborien9777]

Thank you Leila, you are the best...

[@LeilaGharani]

You're welcome 😊

[@warrenhall1750]

Thanks for the wonderful recap of a few of the great methods available via power query.

[@KarinaStavenes]

Amazing! It's so helpful to see where all these different commands are. Excel's features are so underused! Thank you so much!

[@LeilaGharani]

Glad it was helpful Karina!

[@brijkishorkatesaria8208]

Excellent teacher of excel

[@hazemali382]

Many Thanks Leila ♥

[@ricardoromo96]

So useful! I did exactly the same thing today with Macros but it's way easier with Get and Transform. Thank you!

[@LeilaGharani]

It really is!

[@cor351]

You always are very educational and show how it can be applied in practice.

[@LeilaGharani]

Glad you like the tutorials.

[@parikshitsrivastava2311]

I m really big fan of your.. your vedio always supports me in my work..

[@LeilaGharani]

Glad to hear that

[@sumitrathod2814]

It's video most useful to us...!!! 👍👍 Great work mam....!!!

[@emmafeng55]

Leila you are Always the best!!!!

[@praveensharma2632]

Hi Leila, You are awesome your every session is so educative for everyone and your miraculous presentation is an icing on cake. Thanks for putting your great efforts and spreading the light of your spritual knowledge.

[@LeilaGharani]

You are so welcome!

[@geminitwin]

This is the most simplest way. Power Query bring so much to the table.🙂 👍🏼 Video

[@LeilaGharani]

Glad you think so!

[@wayneedmondson1065]

Hi Leila.. great video! You are making me better at Power Query with each one :)) Thanks for the great examples, instruction and courses from your channel and web site. Thumbs up!!

[@LeilaGharani]

Thank you, Wayne!

[@balurock2749]

Thanks a lot and your training videos are awesome :)

[@jigneshsoni2854]

Thank you for sharing. You explain very well!! God bless you

[@Luciano_mp]

Very good Leila, great tips and thanks for the spreadsheet available. Thank you.

[@LeilaGharani]

My pleasure Luciano!

[@odelakumar06]

Excellent teaching

[@excelisfun]

Power Query Rules : )

[@LeilaGharani]

Very true Mike! So do your tutorials on Power Query : )

[@mohammadalbizri2013]

Appreciated your kind efforts.

[@Sarunsherief]

Leila you are the best ❤️ Thanks for sharing this video We are always learning something new every Thursday 😊

[@louism.4980]

Fantastic example, thank you! :)

[@LeilaGharani]

Glad you liked it!

[@petersonwanjau5604]

Always delivering new stuff from what i know. Thanks and keep it up

[@jaydeepnair]

Saved my day...Excellent...

[@kasunchathuranga3556]

Leila can we remove same amount of negative and plus values by using power quaries as duplicates

[@vikaskumar-oj2fr]

Hi You are a great teacher for learning excel Thanks. But this 'download workbook link ' in the description is not working ... Website not working.

[@cesarths10]

This just blew my mind. Great work.

[@LeilaGharani]

Glad you liked it!

[@jessiesi1412]

Thanks for the great video. If the data has been loaded to excel, could we still go back to make the change to the query? If yes, how could we get it back to the query? Thank you.

[@mohammedferasalwattar4175]

Very helpful and useful! Thank you very much.

[@eslamfahmy87]

Actually, so far so good 👍

[@figgeboksjo]

Brilliant, so useful and professionally presented. Thanks!

[@LeilaGharani]

Glad you enjoyed it!

[@moazzamkhan7914]

Always get to learn new things..Thanks Leila

[@LeilaGharani]

My pleasure!

[@HunterGyp]

I have watched videos (your & others) about how to split text by the delimiter and how to use power query to remove them but have not seen one about how to put them in. I need to add some into a string of numbers and still have it come out formatted as text column. I am unable to post here a screen shot of what I need but I have examples.

[@HusseinKorish]

Absolutely Amazing ... thanks Liela

[@robertakwasiadjei8223]

Thanks Leila. Good Morning

[@LeilaGharani]

Good morning!

[@taheralbalushi2831]

Thanks a lot. Brilliant

[@vijaygusain119]

Great Video. My only concern is if our source file gets corrupted or deleted then can we just create another one with same name and same data format and the query will still recognise it!

[@stevecloud4soft]

Delimiter function will work on uniform data. For ununiform data please share a video on how to use it with a combination of Text.Range /Text.middle and Text.Length functions.

[@ahmedfadali4064]

You're great as usual.

[@seizuresaladventures]

Super practical and useful examples,as usual! Thank you, Leila!

[@LeilaGharani]

Glad you think so!

[@kvlpnd]

I used Text to column with similar tasks. However for number in middle I would have to use left function after applying text to column. Unfortunately I will have to continue that practice since my office PC doesn't have office 2016.

[@miguelangelrebolledo5115]

Greats Tips Leila... Thanks. 🇨🇱

[@LeilaGharani]

Glad you think so Miguel!

[@smartzthiha]

Thanks Leila. That helps a lot.

[@LeilaGharani]

You're welcome 😊

[@CarlosAguilar-xw7ot]

Leila, I've learned so much from you, thanks. I'm a mexican fan of you. Could please tell me what brand and model of lap top you use?

[@LeilaGharani]

Glad to hear it Carlos. I usually use a Surface.

[@andrzejwroblewski08]

I love Power Query by You Leila, thanks :)

[@mariostudio7]

Wow a whole new world of Excel! Extremely helpful!!

[@LeilaGharani]

It really is. One of the best tools in Excel!

[@neelamzaidi1683]

You made me love working In Excel! I would love to meet you one day

[@chrism9037]

Great video Leila! I love PQ

[@LeilaGharani]

The best!

[@fuad471]

Thank you great instructions!

[@hajajahirhussain6814]

I recently joined your vba course in Udemy. It's awesome.. thanks!!!

[@LeilaGharani]

Glad you enjoy it!

[@hajajahirhussain6814]

@@LeilaGharani I can't find your Power Query Course in Udemy? Is it there?

[@EduCastingOnlineZA]

Love watching your videos, you make it look real easy.. . Thank you

[@LeilaGharani]

Thanks for watching!

[@zaighamuddinfarooqui1705]

Awesome. Very useful.

[@engmas20091]

Thank you for your distinctive tactics and I wish you luck and success. I have a question in the Excel diagram. How can interstitial values be found through a curve that cannot be equated to, for example when drawing a relationship between some of the values associated with a time period, also how can two points be crossed Two curves (equations cannot be found for them) knowing that the curve points do not show the points of these two points.

[@fergaltierney8672]

Wow. Thanks for that. Why have I never known about Power Query? Thursdays will never be the same again...

[@LeilaGharani]

I'm happy you discovered it now. Better late than never :)

[@HeyLookWhatICanDo]

So good! You are such a boss

[@LeilaGharani]

Thanks Nathan!

[@jyothinarendra993]

It's helpful to me a lot. Thanks for sharing the knowledge

[@LeilaGharani]

It's my pleasure

[@atsrikumar1307]

Great effort!!!

[@LeilaGharani]

Thank you! Cheers!

[@Stepford]

Great, useful tips and very well presented as per usual Leila! Thanks for sharing :)

[@zvikabar-kochva3641]

Thanks Laila for yet another informative and educational video. I'm straggling though with trying to split a column by Euro symbol as delimiter (€). Any idea how to accomplish that?

[@QuangTran-vz8dv]

Thanks so much, Ive learned so much from your shared. Keep it up pls

[@LeilaGharani]

Thanks, will do!

[@fareedmk]

Love it, thanks

[@vijayarjunwadkar]

Great Leila! You saved my day! Wanted these ideas for a data task and found it just in time! Thanks! :-)

[@LeilaGharani]

Glad it was helpful!

[@smilejs]

Thank you.

[@redouanechaouki2606]

Great tutorial, thanks! Sadly authoring in the Power Query Editor is not yet supported for Microsoft Excel 365 for Mac (version 16.48). Hopefully this will be available soon. For now Excel for Mac supports Power Query refresh for many data sources, as well as query creation through VBA.

[@nairsantosh17]

Hi I have a general query on how to improve the file sizes when playing with huge data in excel .my excel files have approx 20 column n 300000 rows data . But after working on it with formula , file sizes become very large n sometimes files don’t open ..can u make some video as how to effective manage file sizes Thanks

[@spuneet156]

This is what is I am working currently, good to use

[@florincopaci6821]

Hello Leila! Thanks for all the information but how can i do the whole power query course because i only got the first 3 sections? Thank you!

[@billykesterguanco2607]

Thanks Leila, how can you remove special characters from Header ROWs/Column Names such as _ or Numbers from Header ROWs/Column Names without doing to rename one by one?