Just to reiterate what most folks have already said. Jon is an excellent instructor. I really appreciate that no time is wasted, yet the pace is such that you can follow along (and or pause and review). He also gives ample context to his information making for a more robust learning experience. I just learned a technique which will save me so much time and frustration using Power Query. I'm bit surprised how simple it can be yet solve various issues I have using data from other sources. Thank you Jon and Happy New Year.

You're the man Jon. Learning PQ in my spare time between projects and raising a baby

@levychr

3 жыл бұрын

Improving yourself in your spare time? That makes you the man as well my fellow excel nerd.

@ExcelCampus

3 жыл бұрын

I'm happy to hear it, Matthew. And I agree with Chris. You are taking on two very challenging tasks, but both are also very rewarding! 🙂Congrats on becoming a dad! 🙌

Thanks for the tip .. makes sense .. do the last name first when there are some with middle name and some without 👍🏼👍🏼👍🏼👍🏼

Excellent way of teaching , split cells in microsoft Excel.

This is easy and excellent way to split text. Thank you trillions!

I have been using power query to pull my reports and I love it. Such a powerful tool!

@ExcelCampus

3 жыл бұрын

It's great to hear that you (and everyone) are using Power Query more frequently.

Beautifully explained, never knew about this process, makes my job so much easy. Thank you Sir ; Much Appreciated

@ExcelCampus

11 ай бұрын

Glad it helped, @user-ku5dm9uo8z ! 😀

Just Joined your POWER QUERY FOR 80% OFF THIS BLACK FRIDAY, Thank you for a great training program

@ExcelCampus

8 ай бұрын

Thank you for joining us! Welcome aboard. 😀

Thanks again Jon. Very helpful!

Another very helpful video. Thanks Jon!

@ExcelCampus

Жыл бұрын

Glad it was helpful, @AnthonyHarris-fe6zo! 😀

Thank you again Jon. I have been following your videos. Please share a video on how to to do this using formulas and text to column as you mentioned in the last part of the video

@ExcelCampus

3 жыл бұрын

Thank you Tridib! We just released a video on Text to Columns (kzread.info/dash/bejne/a5aV1NOJf5XWpso.html) and will do one on formulas in the future.

Thanks Jon you always make Excel fun to work with!

@ExcelCampus

3 жыл бұрын

Thanks so much for your support! I'm happy to hear you are enjoying working with Excel. 👍🙂

Thank you so much for your clear instructional video.

@ExcelCampus

3 жыл бұрын

Thanks M! 🙌

thank you ..

Awesome! Thanks for making this video! You've made it super easy.

@ExcelCampus

3 жыл бұрын

Thank you Misses James! 🙂

Hi Jon. Love Power Query! Thanks for the tips :)) Thumbs up!!

@ExcelCampus

3 жыл бұрын

Thank you, Wayne! 🙌

I watched the Text to Columns video first and then came here. Both are wonderful solutions I would love to see a LAMBDA/LET solution. I have seen a few videos from other creators I watch but I’m curious what you would do. You always amaze me

@joukenienhuis6888

8 ай бұрын

That is also possible, but that destroys the possibility of automation. Because you have to enter the formula each time you get new information and with power query you just have to push one button.

Super Cool Tutorial...Thank You Jon :)

@ExcelCampus

3 жыл бұрын

Thanks Darryl! 👍

Nice! Thanks

Another great video Jon - Thanks for providing these instructional videos. I was confused when following along and couldn't find "From Table/Range" in the menu. Appears that MS recently renamed "From Table/Range" to "From Sheet". Same functionality to follow along.

@ExcelCampus

3 жыл бұрын

Thanks Antony! Yes, the button was recently renamed. I'm not sure which channels of Microsoft 365 it has been released too. It's definitely a bit confusing at first. I'm still trying to get used to it. I'm a creature of habit I guess... 🙂

John!! This tool is a saviour! We pull market reports from some websites (many actually :p). All the reports are in form of excel and then we clean and colate those files into our tempalate. Power query will be very helpful in cleaning. Can we automate the process to instruct excel to pick the data from file in the web using a search criterie ie. property address? Some thing like this would help us save hours :D

@ExcelCampus

3 жыл бұрын

Hi Vasist, I'm happy to hear you are making use of Power Query. In regards to your question, pulling data from the website and passing in the address will largely depend on how the website is built. You can pass URL parameters through to the site for the source of the query, so you can potentially make this dynamic if the site uses the address or some other identifier in the URL of the webpage. Something like: websitename.com/address=123-Main-St-City-State-Zip I hope that helps. Thanks again and have a nice day! 🙂

@vasistreddy968

3 жыл бұрын

@@ExcelCampus Thanks a lot Jon! The URL does'nt seem to be and most of the websites has dropdown to select he market and some of them shows a link like abc.xyz.com/property/313295ca-f0a5-49e7-96f2-2a7bef529e7e! Currenly i have automated the pulling of numbers into the model and have received a lot of appreciation from seniors at the office. Cannot Thank you enough for your videos which provide solution to almost all the problems!

Great video and powerful too. Was wondering how would you reverse this if was to set as 2 columns alone with f name & s name

Many Thanks Jon! It's so easy to split a text by any type of character (either space or any special character) and your one is very easy to replicate. but I have a question for you. My text has like this "PointA - PointB". Sometimes, this text is like "PointA to PointB". I can easily split this two texts by your mentioned easy formulas. but, how can i split these 2 types of text in a single formula? I think, I should use "if" function here but i can't figure it out by myself. could you please help?

Hi Jon. I have learned a lot from this new tutorial video and have applied it at work. However, I have encountered a situation wherein the full name of our employees are like these examples: Mary Ann T. Dela Cruz and John Michael Von P. Del Mundo and Geraldine Hope S. Del Sol. I have difficulty splitting their family names (2 words) and first names (2 to 3 words) into one column. I hope you can help. Thank you.

@joukenienhuis6888

8 ай бұрын

That is a very hard job, because every last name can be different, hence you don’t know how many first and how many last names there are, so telling where to split. If you know they have only two last names, you could split with a space from the right and then combine them, but I don’t know if that would work

Hi John, Thank you for posting such great videos. I need help. I have try & change my excel sheet but after the changes the sheet is not responding to my command with the new changes. Van you offer guidance?

Thanks Jon ! As always, quite informative and to the point ! I am very much interested in finding the best technique or option to convert the text files (plain or delimited) which contain one single line of data in multiple rows due to a LOT OF columns. I presently use first textjoin (multiple rows) and then split them into multiple columns on a single row to achieve the desired results. But recently I had to tackle a lot of files from bank servers, where a single report file contain 79 different reports, each having different number of columns, and hence different number of rows for a single line of information. Any suggestions as to the best way to do it.

@ExcelCampus

3 жыл бұрын

Hi Ullu, Great question! We actually just had this question come up a few times in our Elevate Excel Training Program's community forum. You can use Power Query to combine rows, and I posted a video in the forum about that last week. The basic process is to use the Group By feature in Power Query and then do a custom modification to the code to use the Text.Combine function. You will also create a grouping for all the other rows so you can expand them back out again. It's not a straightforward process but is possible with Power Query. I hope that helps. Thanks again and have a nice day! 🙂

@ExcelCampus

3 жыл бұрын

And I should have mentioned that I'll add this to our list for future KZread videos.

Hey Jon, really find your videos useful, however, my Power Query Editor toolbar looks different to yours, I don't have a "split column" button and can't seem to edit the toolbar to add one in. Any idea why our toolbars are different?

PQ is a powerful tool. The problem is that there is no comparison between Excel 2010/2013 PQ and Excel 2019/365 PQ when in comes to splitting columns. Older version of Excel have fewer features .....not all your viewers have Office 365 therefore it might be useful to present solutions both in Office 363 as well as older versions in Excel.....other than that a good video 👍👍👍

@ExcelCampus

3 жыл бұрын

Hi Planiorlro, Thank you for the suggestion. I believe PQ on Excel 2010 and 2013 has the Split Column feature. So you should be able to do this technique on any version of Excel that has PQ (2010 or later).

I have a list of names and a few names have a suffix (i.e. II or IIII). So some names have three fields and some have four. Doing the first split places the suffixes with the last names. Is there a way to get around this? Thanks.

Hi sir thanks for your knowledge sharing. one help sir.one column more than 5000 rows but I need to filter based on column. Filter table icon not show more than 1000. example colum ( Bulldozer, Additional Bulldozer, Buldozer 23, December Bulldozer, Aple, 55553,2055) i don't want Bulldozar ralated data in my original Data. how to do in Power quer or any other way. please help this case.

I'm interested in the formulas and text to columns please.

@ExcelCampus

3 жыл бұрын

Thanks for the vote, Misses James! We'll publish videos on those topics in the next few weeks.

can you make a video about how to merger column and ignore blanks in power query?

@ExcelCampus

3 жыл бұрын

Hi Mohamed, Great question! Yes, we will put this on our list for future videos.

I wanna to do certificate course from your tutorials point,is it possible sir?

@ExcelCampus

3 жыл бұрын

Hi Shiva, thank you for your interest. You may join us with our Elevate course. Elevate Excel is a comprehensive (beginner-to-advanced) online training program that covers everything you need to drastically improve your Excel skills. You may check this link www.excelcampus.com/elevate-excel-invite/

I am looking to isolate text in my bank transactions for my budget, but the string of text prior to the name of the item is not consistent. Is there a way to isolate those?

how to seperate WAUSA in WA and USA in two seperate coloumns in power query

Hello Jon I want to split without a char! for example 0011111001110100111110001111001 The Reason are that's the workingdays on May from germany I want to get something like this 0,0,1,1,1,1,1 ...... have you an idea how can i do this with Power query?

@ExcelCampus

3 жыл бұрын

Hi Klaus, Great question! Yes, there are few ways to go about it. Here are some suggestions: 1. If you want the numbers in separate columns you can use the By Number of Characters option on the Split Column Menu. You will type a 1 in the box to split at each character, and select Repeatedly for the Split option. That will add several columns with each character in it's own column. 2. If you want to have the results in a single column but separated by commas, you could do a Replace Values step for both the 1 and 0. You would replace 1 with 1, and replace 0 with 0, That technique will work if you only have 1's and 0's, or just a few different characters in the values in your column. Otherwise, you can split by character and then merge the columns back with a separator character. I hope that helps. Thanks again and have a nice day! 🙂