Hi Ilyas, Sure thing. I recently upgraded my computer. This was mostly to help with the video recording/production that I do and to have more resources for the live recordings and virtual meetings. Another benefit is that Excel and Power Query run very fast as well. I'm using a higher end gaming PC from MSI. I don't do much gaming, but gaming PCs have powerful processors and graphics cards. Here are the specs of the PC. Intel Core i7-10700KF - 32GB Memory - NVIDIA GeForce RTX 3080 - 1TB SSD I don't think you'll need a graphics card like that for Excel. And that is where a lot of the expense in the gaming PC's come in. So you can probably find a more affordable PC with a good processor and memory. This is a PC, but you can find similar specs in laptops too. I hope that helps. Thanks again and have a nice weekend! 🙂
If you’d like to read the accompanying blog post on my website, you can access the file here under "Download the Excel Files": www.excelcampus.com/powerquery/combine-text-formulas-power-query
Hi Om, I explain how to include the middle name column in the merge in the section on Power Query. It is at about the 6:30 mark in the video. I hope that helps. Thanks again and have a nice weekend! 🙂
And I should have mentioned that the video I linked to was about splitting text with Flash Fill. You can also use it to combine text, which I believe is what your suggestion was, and I forgot to mention that in this video. So thanks for the feedback! 👍
Hi John. I would like to be able to copy data from one excel file to another. Also, I would like it to be dynamic, i.e. when I override the excel file (the one I copy data from) with a new set of data, the details in the first file will update. Which of your courses do I do to be able to do that?
Merge Columns is Greyed Out: I can't select it. I'm trying to grab the address columns, which is separated into columns for number, street, suffix, direction, apt. I would like to put all of these columns into one and then use that to load up my database in another program. How can I do it and not have the resulting Formulas in the data table and just have the raw data, but combined in one column called "street address"? Thanks!
Is there any solution to vlookup from multiple value cell. Like A1 =25, A2=7|25, A3=0, A5=0|7|25..desired result B1, B2, B5 should be mark as positive match...pls guide
Hi Nathan, Great question! Yes, it's definitely possible and there are many ways to go about it. With Power Query you could do the Merge Columns in two separate steps. First, merge the First and Middle column with a space separator. Then merge that column with the Last name and use the comma separator. With formulas you could use an IF formula to check if the middle name exists. Here is an example. =C2&", "&IF(ISBLANK(B2),A2,A2&" "&B2) In this example the IF formula is testing if the middle name (B2) is blank using the ISBLANK function. If it is blank, then only A2 is returned. If not, then A2 and B2 are returned, separated by a space. Like I said, there are a lot of possibilities for solving this, but that should get you started. I hope that helps. Thanks again and have a nice weekend! 🙂
Hi I have 2 columns containing formulars, that I wish to combine into a single column is that possible ? Both the columns stream live stock market data
Hi Dave! 😀 Yes, that is possible. If the data is in Columns B and C, then =TEXTJOIN("|",,B2,C2) could be placed in Cell D2 and filled down. Hope that helps! 😀
What about the reverse? I have a huge file that is BrandName Product Size all in one column & I need to split into 3 easily (Flash fill isn't quite doing the job as its sometimes different amounts of letters in between so its not pickign it up) e.g Gilmours Flour 10KG Bluebird Chips Salt & Vinegar 10 x 25G Need to split into 3x columns
Hey Charlotte, Great question! We do have a recent series of videos on different techniques for splitting cells using Power Query, formulas, Flash Fill, and Text to Columns. However, this example is going to be more complex than the examples used in those videos. The first challenge is splitting the brand name and product name. In the example you gave, is Bluebird Chips the brand name? Or is it just Bluebird? If the brand name can contain two or more names, and I assume it can, then it's going to be a challenge to split those two names. One possible way is to use a fuzzy match or partial match lookup (join/merge) with Power Query. For this you would need to have a separate table with a list of the brand names. You could then do a partial match lookup from the combined data to the brand list table, and return the brand name only. This would give you the brand name, which you could then remove from the combined data column with something like the Replace function to replace the brand name with nothing. This would leave you with the product name and size in the cell. You could then use a formula to find the first number in the cell, which would be the starting point for the size. That would only work if there are no numbers in the product names. If you were able to use that technique to find the first number, you could then use the LEFT and RIGHT functions to return the product name and size, based on the character number found in that number search. Those are just some ideas to try and automate this process. There might be other ways to go about it as well. I think this would make a great challenge for our community. If you have a longer example file, feel free to send it over to support@excelcampus.com and we'll add it to our list for future posts. Thanks again and have a nice weekend! 🙂
@@ExcelCampus Hi Jon, Thanks for the reply!! Yes, it is often two things in the Brand name making it a bit of a time consuming process to do manually :( I will give your suggestions a go when I get back to work & send over a file to the email, it might be a good one to show people, as it is a bit of a pain in the neck! I wasn't sure if it was one of those "ahh that's easy you just do xyz" kind of things, as I had that experience the other day (the file initially started all in caps lock but needed to be lower case with first letter capitalised) so I discovered the proper() function which saved me hours lol ... (I only started using excel a few months ago, so still learning a lot as I go)
Hi Jon, First of all thank you for presenting my name within your video, much appreciated 😊. Second of all, this video highly helpful 👏👌. Do you have a direct email address where I can email you on as I have a few questions in mind that I would like to ask you directly. Regards Prof YC
Hi Prof. Y.C 2020 you may email us at jon@excelcampus.com however, we cannot guarantee the response time due to high volume requests from our members. You may also subscribe to our newsletter by following our website www.excelcampus.com/free 😀
I clicked like b4 watching cos it's your video Jon. Quality assured from the kindest tutor on youtube. 💪🙂
Thank you my best teacher for helping
Tks! This help a lot...and I use this option directly on PBI.
Simple and comprehensive, thank you
Amazing Vídeo Jon, flash fill is other option too. I love PowerQuery! Congrats from Brazil
Thank you for the feedback and suggestion, Michel! 🙂👍
Uau, você aqui, 2 grandes mestres do excel!
thank you jon, your video very usefull
Great Video. Thanks for sharing.
Thanks Cici! 🙂
Great! Thank you!
This is really amazing and helpful. Thank You
Thanks so much SK! 😊
Thank you, very helpful.
Thanks Lorraine! We appreciate your support. 🙂
Thank you, John.
Thanks Pat! 🙂
Really Super Cool Tips...Thank You Jon :)
Thanks Darryl! 🙌
Great video as always, Jon! Thanks a stack!
Hi Jon. Some great text combination tips! Thanks for sharing :)) Thumbs up!!
Thanks so much Wayne! Have a great weekend! 🙌
@@ExcelCampus Thanks Jon! Likewise :))
6:39 Thankyou😘
Textjoin 👍
Please post the webinar you mentioned. Thanks
To join our free webinar please follow this link www.excelcampus.com/webinar 😀
Thank you very much, Jon!
Can you please tell us what hardware you use that your Excel runs so fast?
Hi Ilyas, Sure thing. I recently upgraded my computer. This was mostly to help with the video recording/production that I do and to have more resources for the live recordings and virtual meetings. Another benefit is that Excel and Power Query run very fast as well.
I'm using a higher end gaming PC from MSI. I don't do much gaming, but gaming PCs have powerful processors and graphics cards. Here are the specs of the PC.
Intel Core i7-10700KF - 32GB Memory - NVIDIA GeForce RTX 3080 - 1TB SSD
I don't think you'll need a graphics card like that for Excel. And that is where a lot of the expense in the gaming PC's come in. So you can probably find a more affordable PC with a good processor and memory.
This is a PC, but you can find similar specs in laptops too.
I hope that helps. Thanks again and have a nice weekend! 🙂
Halo i am from Indonesia i am verry interest for your vidio
please share this doc for my, because i want to learn exel. Thks
If you’d like to read the accompanying blog post on my website, you can access the file here under "Download the Excel Files": www.excelcampus.com/powerquery/combine-text-formulas-power-query
Awesome! Will you also be showing how to merge the middle name in power query in case we have any?. Thank you!
Hi Om, I explain how to include the middle name column in the merge in the section on Power Query. It is at about the 6:30 mark in the video.
I hope that helps. Thanks again and have a nice weekend! 🙂
@@ExcelCampus Thank you very much!
So in what situations do you use the CONCATENATE function?
I am watching this on my phone so it will be helpful if you zoom in to the action.
Thank you for the suggestion! We will try to apply more zoom in future videos.
What about flash fill? Great video thanks for sharing
Hi FRANKWHITE1996 here's the link to the magical feature of Flash th-cam.com/video/JigehyeACcw/w-d-xo.html 😀
@@ExcelCampus great! Thanks! 🙌
And I should have mentioned that the video I linked to was about splitting text with Flash Fill. You can also use it to combine text, which I believe is what your suggestion was, and I forgot to mention that in this video. So thanks for the feedback! 👍
hi John, after reaching the last column in excel which is the XFD , is there a way i can still add columns ? Thank you.
Thanks for your videos. where do I find the link for the webinar about PQ, PP etc?
Please post the link on the webinar you mentioned. Thanks.
To join our free webinar please follow this link www.excelcampus.com/webinar 😀
Hi John. I would like to be able to copy data from one excel file to another. Also, I would like it to be dynamic, i.e. when I override the excel file (the one I copy data from) with a new set of data, the details in the first file will update. Which of your courses do I do to be able to do that?
Merge Columns is Greyed Out: I can't select it. I'm trying to grab the address columns, which is separated into columns for number, street, suffix, direction, apt. I would like to put all of these columns into one and then use that to load up my database in another program. How can I do it and not have the resulting Formulas in the data table and just have the raw data, but combined in one column called "street address"? Thanks!
Is there any solution to vlookup from multiple value cell. Like A1 =25, A2=7|25, A3=0, A5=0|7|25..desired result B1, B2, B5 should be mark as positive match...pls guide
Can you do Last, First Middle? What is the method for that?
Hi Nathan, Great question! Yes, it's definitely possible and there are many ways to go about it.
With Power Query you could do the Merge Columns in two separate steps. First, merge the First and Middle column with a space separator. Then merge that column with the Last name and use the comma separator.
With formulas you could use an IF formula to check if the middle name exists. Here is an example.
=C2&", "&IF(ISBLANK(B2),A2,A2&" "&B2)
In this example the IF formula is testing if the middle name (B2) is blank using the ISBLANK function. If it is blank, then only A2 is returned. If not, then A2 and B2 are returned, separated by a space.
Like I said, there are a lot of possibilities for solving this, but that should get you started.
I hope that helps. Thanks again and have a nice weekend! 🙂
@@ExcelCampus thanks. I was hoping for a one step solve all, but that will have to work.
Hi
I have 2 columns containing formulars, that I wish to combine into a single column is that possible ? Both the columns stream live stock market data
Hi Dave! 😀 Yes, that is possible. If the data is in Columns B and C, then =TEXTJOIN("|",,B2,C2) could be placed in Cell D2 and filled down. Hope that helps! 😀
Works perfectly, spent ages looking for this,. Thank You@@ExcelCampus
What about the reverse? I have a huge file that is BrandName Product Size all in one column & I need to split into 3 easily (Flash fill isn't quite doing the job as its sometimes different amounts of letters in between so its not pickign it up)
e.g
Gilmours Flour 10KG
Bluebird Chips Salt & Vinegar 10 x 25G
Need to split into 3x columns
Hey Charlotte,
Great question! We do have a recent series of videos on different techniques for splitting cells using Power Query, formulas, Flash Fill, and Text to Columns. However, this example is going to be more complex than the examples used in those videos.
The first challenge is splitting the brand name and product name. In the example you gave, is Bluebird Chips the brand name? Or is it just Bluebird?
If the brand name can contain two or more names, and I assume it can, then it's going to be a challenge to split those two names.
One possible way is to use a fuzzy match or partial match lookup (join/merge) with Power Query. For this you would need to have a separate table with a list of the brand names. You could then do a partial match lookup from the combined data to the brand list table, and return the brand name only.
This would give you the brand name, which you could then remove from the combined data column with something like the Replace function to replace the brand name with nothing.
This would leave you with the product name and size in the cell. You could then use a formula to find the first number in the cell, which would be the starting point for the size. That would only work if there are no numbers in the product names.
If you were able to use that technique to find the first number, you could then use the LEFT and RIGHT functions to return the product name and size, based on the character number found in that number search.
Those are just some ideas to try and automate this process. There might be other ways to go about it as well. I think this would make a great challenge for our community. If you have a longer example file, feel free to send it over to support@excelcampus.com and we'll add it to our list for future posts.
Thanks again and have a nice weekend! 🙂
@@ExcelCampus Hi Jon,
Thanks for the reply!! Yes, it is often two things in the Brand name making it a bit of a time consuming process to do manually :(
I will give your suggestions a go when I get back to work & send over a file to the email, it might be a good one to show people, as it is a bit of a pain in the neck!
I wasn't sure if it was one of those "ahh that's easy you just do xyz" kind of things, as I had that experience the other day (the file initially started all in caps lock but needed to be lower case with first letter capitalised) so I discovered the proper() function which saved me hours lol ... (I only started using excel a few months ago, so still learning a lot as I go)
Hi Jon,
First of all thank you for presenting my name within your video, much appreciated 😊.
Second of all, this video highly helpful 👏👌.
Do you have a direct email address where I can email you on as I have a few questions in mind that I would like to ask you directly.
Regards
Prof YC
Hi Prof. Y.C 2020 you may email us at jon@excelcampus.com however, we cannot guarantee the response time due to high volume requests from our members. You may also subscribe to our newsletter by following our website www.excelcampus.com/free 😀
@@ExcelCampus Thanks for that Jon 😊👍