How to Split Cells & Text in Excel with Power Query

แชร์
ฝัง
  • เผยแพร่เมื่อ 16 ก.ย. 2024

ความคิดเห็น • 60

  • @shabustinkslol
    @shabustinkslol 3 ปีที่แล้ว +5

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

    • @levychr
      @levychr 3 ปีที่แล้ว +5

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

    • @ExcelCampus
      @ExcelCampus  3 ปีที่แล้ว +2

      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! 🙌

  • @musk4mars116
    @musk4mars116 2 ปีที่แล้ว +3

    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.

  • @vikramraghuwanshi9455
    @vikramraghuwanshi9455 3 ปีที่แล้ว +1

    Excellent way of teaching , split cells in microsoft Excel.

  • @davidshamiri1448
    @davidshamiri1448 3 ปีที่แล้ว +2

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

  • @PrabhakarMurthy-y5s
    @PrabhakarMurthy-y5s 9 หลายเดือนก่อน

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

    • @ExcelCampus
      @ExcelCampus  9 หลายเดือนก่อน

      Thank you for joining us! Welcome aboard. 😀

  • @esthersuh3388
    @esthersuh3388 ปีที่แล้ว +1

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

  • @PrabhakarMurthy-y5s
    @PrabhakarMurthy-y5s ปีที่แล้ว +1

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

    • @ExcelCampus
      @ExcelCampus  ปีที่แล้ว +1

      Glad it helped, @user-ku5dm9uo8z ! 😀

  • @mrmusicloverable
    @mrmusicloverable 3 ปีที่แล้ว +2

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

    • @ExcelCampus
      @ExcelCampus  3 ปีที่แล้ว +1

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

  • @AnthonyHarris-fe6zo
    @AnthonyHarris-fe6zo ปีที่แล้ว +1

    Another very helpful video. Thanks Jon!

    • @ExcelCampus
      @ExcelCampus  ปีที่แล้ว

      Glad it was helpful, @AnthonyHarris-fe6zo! 😀

  • @edilpoulina1870
    @edilpoulina1870 3 ปีที่แล้ว +1

    Thanks again Jon. Very helpful!

  • @provetome1199
    @provetome1199 3 ปีที่แล้ว +1

    Thanks Jon you always make Excel fun to work with!

    • @ExcelCampus
      @ExcelCampus  3 ปีที่แล้ว

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

  • @tridibbiswas3361
    @tridibbiswas3361 3 ปีที่แล้ว +1

    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
      @ExcelCampus  3 ปีที่แล้ว

      Thank you Tridib! We just released a video on Text to Columns (th-cam.com/video/9barqPO2pvg/w-d-xo.html) and will do one on formulas in the future.

  • @notanaive
    @notanaive 3 ปีที่แล้ว +1

    Thank you so much for your clear instructional video.

  • @713mrsjames
    @713mrsjames 3 ปีที่แล้ว +1

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

    • @ExcelCampus
      @ExcelCampus  3 ปีที่แล้ว

      Thank you Misses James! 🙂

  • @wayneedmondson1065
    @wayneedmondson1065 3 ปีที่แล้ว +1

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

    • @ExcelCampus
      @ExcelCampus  3 ปีที่แล้ว +1

      Thank you, Wayne! 🙌

  • @yvonneee2175
    @yvonneee2175 2 ปีที่แล้ว +1

    thank you ..

  • @patrickschardt7724
    @patrickschardt7724 3 ปีที่แล้ว +1

    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
      @joukenienhuis6888 10 หลายเดือนก่อน +1

      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.

  • @darrylmorgan
    @darrylmorgan 3 ปีที่แล้ว +1

    Super Cool Tutorial...Thank You Jon :)

  • @AntJK2008
    @AntJK2008 3 ปีที่แล้ว +1

    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
      @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... 🙂

  • @shoppersdream
    @shoppersdream 3 ปีที่แล้ว +1

    Nice! Thanks

  • @jugeljumamoy9567
    @jugeljumamoy9567 3 ปีที่แล้ว +1

    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
      @joukenienhuis6888 10 หลายเดือนก่อน

      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

  • @mariamckillop8248
    @mariamckillop8248 3 ปีที่แล้ว

    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?

  • @vasistreddy968
    @vasistreddy968 3 ปีที่แล้ว +1

    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
      @ExcelCampus  3 ปีที่แล้ว +1

      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
      @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!

  • @amineunshakable1295
    @amineunshakable1295 2 ปีที่แล้ว

    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

  • @sojolhamid6356
    @sojolhamid6356 3 ปีที่แล้ว

    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?

  • @ulludacharkha
    @ulludacharkha 3 ปีที่แล้ว +1

    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
      @ExcelCampus  3 ปีที่แล้ว +1

      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
      @ExcelCampus  3 ปีที่แล้ว

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

  • @Mike20878
    @Mike20878 2 ปีที่แล้ว

    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.

  • @mohamed.montaser
    @mohamed.montaser 3 ปีที่แล้ว +1

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

    • @ExcelCampus
      @ExcelCampus  3 ปีที่แล้ว

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

  • @samwatson2843
    @samwatson2843 2 ปีที่แล้ว

    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?

  • @user-ir1fc1wh8z
    @user-ir1fc1wh8z 7 หลายเดือนก่อน

    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.

  • @713mrsjames
    @713mrsjames 3 ปีที่แล้ว +1

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

    • @ExcelCampus
      @ExcelCampus  3 ปีที่แล้ว

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

  • @planiolro
    @planiolro 3 ปีที่แล้ว +1

    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
      @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).

  • @FiveSolasMap
    @FiveSolasMap 2 ปีที่แล้ว

    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?

  • @jishnu571
    @jishnu571 2 ปีที่แล้ว

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

  • @ShivaYadav-zt6tn
    @ShivaYadav-zt6tn 3 ปีที่แล้ว +1

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

    • @ExcelCampus
      @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/

  • @KlausTrapp
    @KlausTrapp 3 ปีที่แล้ว +1

    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
      @ExcelCampus  3 ปีที่แล้ว +1

      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! 🙂