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

Elevate your Excel skills with this informative tutorial, perfect for anyone looking to handle complex text transformations effortlessly. Discover the power of Power Query (Get & Transform) in Excel, a tool that simplifies what would otherwise be complicated tasks using formulas.

Here's a glimpse of what you'll learn:
Overview of Text Transformation Challenges: Understand common problems like separating words by delimiters, splitting columns based on logic, and extracting specific text parts, such as numbers from strings.

Power Query vs. Excel Formulas: Learn why Power Query often becomes a more efficient choice over traditional Excel formulas, especially for complex tasks.

Step-by-Step Demonstrations: Follow practical examples that include splitting department and position data into separate columns, extracting employee numbers with specific formats, and dividing full names into first and last names with proper capitalization.

Using Power Query Functions: Get familiar with Power Query features like 'Split Column', 'Extract', and 'Add Prefix', which make text transformations a breeze.

Dynamic Data Updating: See how Power Query allows for easy updates to your data transformations, saving time and effort in managing ongoing data changes.

Final Data Refresh Demonstration: Watch a live demonstration of adding new data to the table and refreshing the query to apply all transformations seamlessly.
Discover the EASIEST way to perform text transformations in Excel. Power Query is the perfect tool for you to solve these tasks that can be quite complicated with just Excel formulas.

Usually, solving these transformations in Excel are NOT an easy task. If you want to do any of the following with Excel formulas, things can get scary very fast:
- Separate words in Excel by a delimiter
- Split a column in Excel based on a certain logic in multiple columns
- Separate text from numbers in a cell
- Extract numbers from the middle of a text string
- Extract text after a specific character in Excel

In this tutorial I'll show you how you can use Power Query to perform some common text transformation tasks. See for yourself how easy these tasks become with some basic Power Query understanding.



🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[IvanCortinas_ES]

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

[scottgaines]

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!

[peteringvorsen]

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 😊

[Sarunsherief]

Leila you are the best ❤️

Thanks for sharing this video
We are always learning something new every Thursday 😊

[ianodonoghue]

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

[kolavithonduraski]

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 !

[KarinaStavenes]

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

[jigneshsoni]

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

[Stepford]

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

[petersonwanjau]

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

[ann-plst]

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.

[warrenhall]

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

[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

[balurock]

Thanks a lot and your training videos are awesome :)

[seizuresaladventures]

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

[praveensharma]

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.

[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.

[farzanmoha]

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.

[mohammedferasalwattar]

Very helpful and useful! Thank you very much.

[aixcheltjarquin]

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.