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 :))
really amaaaaaaaaaaaaaaaaazin ,, thanks a lot ,, quite voice ,, striate forward without more intro pulse many many info in few minutes ,, really thanks
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?
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
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.
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}
@@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
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 ..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
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.
@@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
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..
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.
@@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.
Thank you for sharing such a valuable knowledge. The unicode trick is gold. Thanks, Mynda and Phil!
Glad you liked it, El!
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 😊
oh man this is awesome!!! Thanks for tying in the explanation with ASCII - it TOTALLY makes more sense now.
Glad to hear it!
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! 😊
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 😊
Hi Mynda/Phil! Thanks for the video. It really helped me with a special project.
Great to hear 😊
I have been looking for this for a long time.. Thanks for this tutorial...
Most welcome 😊
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!
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!
Thanks a lot, Mynda & Philip
Excel-lent, as usual.
😊 Thanks, Meni!
Hi Mynda/Phil!Great Tutorial From Phil Really Well Explained...Thank You Both :)
Cheers, Darryl!
You are awesome! It looks like game when you present it : )
😊 thank you!
Fantastic tip, great. Thank you Mynda!
Our pleasure, Luciano!
Unicode tip was very helpful
Great to hear 😊
Very helpful Mynda, thanks!
Cheers, Chris 😊
Super useful for so many scenarios. Thanks!
Great to hear, Joe!
Thanks alot for all these great lessons.
Glad you like them!
Great explanation! Super useful!
Glad to hear that!
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 😀
super again it's very informative and useful to clean data.
Glad you liked it, Sonu!
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!
Well done. Thank you.
Thank you too!
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" })
SO helpful! Thank you!!
Great to hear 😊
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.
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}
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
always more that great
Thanks so much!
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 ..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 tip. Thanks
Glad it was helpful, Gordon!
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.
Awesome. Thanks.🙏
My pleasure 😊
Amazing, thank you.
Glad you liked it!
This helps alot! Great!
Pleased to hear that that, Rasmus 😊
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
Great so easy ...to seprate the int and string...
Great to hear 😊
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
Amazing!Thank you a lot!
You’re welcome 😊
Amazing!!
Thank you! Cheers!
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
Great Content!
Cheers, Justin!
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
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.
Is there no power query editor on mac?
Not yet :-(
Fantastic!
Glad you like it!
Wow love it
Great to hear, Shorya!
pure genius!
Glad you liked it, Hajera!
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?
Awesome!
Glad you liked it 😊
Thanks
Welcome 😊
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
Amazing..
Thanks a lot 😊
This is lit.
Glad it was helpful 😊
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
Very nice, thank you !!
My pleasure 😊