Clean and Extract Text the EASY Way - Pro Tip!
Вставка
- Опубліковано 6 вер 2024
- With Power Query we can quickly and easily extract text strings, whether it's letters, either upper or lower case, or numbers, and even special characters. Download the sample Excel file here: bit.ly/3okDqDa
0:16 Introduction
0:42 Text.Select Lower Case Letters
1:28 Text.Select Upper Case Letters
1:48 Text.Select Numbers
2:12 Text.Select Letters and Numbers
2:52 Text.Remove Lower Case Letters
3:42 Text.Remove Upper Case Letters
3:51 Text.Remove Numbers
4:06 Text.Remove Letters and Numbers
4:28 Text.Remove Special Characters
5:35 Unicode Trick
View our comprehensive courses: www.myonlinetr...
Excellent tutorial. I really appreciate how clearly everything is explained and demonstrated.
Thanks for your kind words, Jim!
Thanks! I use your videos as a part of my weekly personal business training. You have taught me so much! Thank you!!!
Awesome to hear, Barbara 😊
Thank you for sharing such a valuable knowledge. The unicode trick is gold. Thanks, Mynda and Phil!
Glad you liked it, El!
I have been looking for this for a long time.. Thanks for this tutorial...
Most welcome 😊
Was searching for some other solution and stumbled upon this video. Helped me solve a problem I was banging my head around for the last three days.
Wow, that’s awesome to hear 😊
It is so crazy that this video just came out. I kid you not I just started a project that needed this as a solution. Thank you!
Great timing! 😊
Unicode tip was very helpful
Great to hear 😊
oh man this is awesome!!! Thanks for tying in the explanation with ASCII - it TOTALLY makes more sense now.
Glad to hear it!
You are awesome! It looks like game when you present it : )
😊 thank you!
Well done. Thank you.
Thank you too!
really amaaaaaaaaaaaaaaaaazin ,, thanks a lot ,, quite voice ,, striate forward without more intro pulse many many info in few minutes ,, really thanks
Thank you so much 😀
Hi Mynda/Phil! Thanks for the video. It really helped me with a special project.
Great to hear 😊
Very nice, thank you !!
My pleasure 😊
Cool...Thats what utilities like ASAP UTILITIES do so i can do this on my own now using Power Query....Very Cool indeed!! Thanks for the Knowledge!!!
Glad it was helpful!
Awesome Mynda and Phil! Thanks for the tips and the explanation of the underlying unicode logic for Text.Select and Text.Remove. Very useful! Thumbs up :))
Cheers, Wayne!
Thanks
Welcome 😊
Amazing!!
Thank you! Cheers!
Thanks alot for all these great lessons.
Glad you like them!
Fantastic tip, great. Thank you Mynda!
Our pleasure, Luciano!
Great so easy ...to seprate the int and string...
Great to hear 😊
Super useful for so many scenarios. Thanks!
Great to hear, Joe!
Thanks a lot, Mynda & Philip
Excel-lent, as usual.
😊 Thanks, Meni!
Great explanation! Super useful!
Glad to hear that!
Fantastic!
Glad you like it!
Hi Mynda/Phil!Great Tutorial From Phil Really Well Explained...Thank You Both :)
Cheers, Darryl!
Very helpful Mynda, thanks!
Cheers, Chris 😊
Really like the way you explain things. I have learnt a lot from your tutorials but in one situation which I am not able to solve. Can you help please?
Great to hear! Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Very useful video! Thank you so much! 😊👍
Glad it was helpful, Vijay!
super again it's very informative and useful to clean data.
Glad you liked it, Sonu!
Wow love it
Great to hear, Shorya!
SO helpful! Thank you!!
Great to hear 😊
Great tip. Thanks
Glad it was helpful, Gordon!
Amazing, thank you.
Glad you liked it!
Awesome!
Glad you liked it 😊
Amazing..
Thanks a lot 😊
Awesome video so useful thanks!
Thanks, Brian! 😊
Fantastic tip, thanks so much
Our pleasure 😊
@@MyOnlineTrainingHub Mynda, can you recommend the right book for me to learn the M language? I tend to use the column from examples rather than coding it myself but I'd like to be able to
Awesome. Thanks.🙏
My pleasure 😊
pure genius!
Glad you liked it, Hajera!
Thanks! wonderful tutorial, How can I get only text (Upper and Lower in the same sequence). I just want to remove the numbers and the special characters? Really appreciate your explanations
Thanks Gaurav. You can use this to extract only letters: Text.Select([Text], { "a".."z", "A".."Z" })
This helps alot! Great!
Pleased to hear that that, Rasmus 😊
Thank you for this. It's very helpful. Are you able to please suggest a solution for an alphanumeric string that I need to clean up and convert to number. It's a banking record. It has a "C" at the end to represent a credit. For instance, 800.00C is meant to be -800.00 on the bank statement. (900.00 is as stated (i.e., debit 900.00). So in this instance, I would like to keep the 900.00 as is and convert all numbers that have a C at the end to a negative number. Thanks for any assistance you can provide.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Thank you. I have just done so.
This is lit.
Glad it was helpful 😊
HI ..How can I remove a particular text from the column from query editor. For instance if I want to remove the word" city "from the text San Juan city
On the Transform tab you'll find 'Replace Values'.
Great Content!
Cheers, Justin!
Great tips. I really think that a native regular expression formula would be nice. I managed to use regular expression in PQ through JavaScript but it is very slow. Do you have any tips?
Hi Carlos, I haven't really used RegEx through JS, maybe if you provide an example I can see how it performs for me.
Hi, if there number rows also included - I want to show only number rows not included alphanumeric rows.. what will be the code we have to write
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thank you very much. I am trying to extract a specific character string of seven characters that starts with a six that is in a text field the size of a large paragraph. I cannot figure out the code to extract when it is starts with a six and those seven characters.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub thank you for replying. I work with gov data and do not feel comfortable trying to turn it into sample data. I will continue looking for the answer.
can you do a video on concatenation. I want to know how to create a text base template. I want to be able to build a template that my students fill in and then the summary of the text appear in another cell, only once the first set of cells have been completed.....could you do a tutorial like that please....thanks
Hi Deborah, it's probably quicker if you post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Great Video! Can this technique be used if desired text is in another sheet? and is a table? Thanks!
Thanks, Andrey! Yes, the text can be in another sheet and is a table.
Thanks for the replay. Maybe forgive my english, but my question is if the desired text {"search 1"... "Seatch 10"} is not in brackets but is a table in another sheet. I do not know the syntaxis but something like {Sheet1!Table_desired_search}
I don't have intellisense when adding custom columns, is this just due to my old excel version (2016) or is there a way to enable it?
Yes, it's due to your version. You can't enable it as such. You have to upgrade to a later version of Excel.
@@MyOnlineTrainingHub thanks
Is there no power query editor on mac?
Not yet :-(
Ma'am in power query, How to solve the problem of 1000 rows limit reached in filter option as I filtered my data but it was applied only on 1000 rows & not working on after 1000 rows.
The filters are applied to the entire dataset upon Close & Load, so it sounds like there's another issue. If you need to see more items in the filter drop down in Power Query, you can click the link at the bottom of the filter drop down to show more.
Hi I have a question : I have a huge list of customers names that start with A to Z and I would like to filter those customers names starting from A to L and only show me other customers names starting with M to Z. Customer names starting from A to L is handled by Group A and Customers names starting from M to Z is handled by group B. How do I filter that using power query?
Thanks in advance..
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
How to remove only first for ex 3 characters?
There's a button on the Transform and Add Column ribbon to Extract the first n characters.
Hi, Thanks for this video! In the last example to extract number how to convert the column to number datatype in the same step?
Please, which version of excel is this?
It's Microsoft 365, but this should work in earlier versions of Excel too..
@@MyOnlineTrainingHub Appreciate it. Still got a problem getting the from table/range menu. After i click on the cell, then go t o the data, i don't see the from table/range. Need a way around this please.
Please post your question and screenshot of your menu on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
how to remove or extract duplicate text in a string? 'dog, fish cat, dog cat/fish cat dog'
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Amazing!Thank you a lot!
You’re welcome 😊