Those are some pretty good tips, paricularly how you sorted out the name string. Unfortunately, the raw data does not always allow for such a straightforwand approach. You may have a few people with 2 middle initials, some academic titles, some Spanish people with 2 last names, all that great fun.
7:50 when you use the PROPER function the S's after the apostrophes are in caps. Is there a way to fix that? 21:00 I didn't need to subtract the SEARCH term to get the full name. You got it right even without that. That shit got too complicated anyways with the - and + in the functions... Thanks.
Good catch, I hadn't noticed that! I don't believe that you can fix that - interesting that excel chooses to capitalize after an apostrophe. Excellent - I always like learning more efficient ways of getting a task done.
When I attempt to execute the flash fill while using the PROPER function (like you do at 7:40), I get an error message stating, "we looked at the data... and didn't see a pattern". So it proceeds to fill a recurring pattern depending on how many cells I manually input. E.g. B's Beverages, Island Trading, B's Beverages... Is there a way to fix this? I don't run into this issue when I just manually type out the name and excel auto-detects the pattern.
I strongly recommend buying the book if you intend on attempting the certification exam. You need to be able to complete all of the practice tasks in the book without reference to the text (or my videos) in order to be confident going into the test. Additionally there will be some questions on the test that refer to concepts contained in the text but are not included in the practice tasks (and therefore not in my videos either).
Indeed, that's one of the many nice things about PQ! I started recording a set a videos on PP, PQ and PBI but they never really gained any traction lol.
@@CarshaltonAdvisory I started to check your videos too recently, because I planned to do this Exam for myself. I mean, I am quite into Excel and PBI and I guess this wouldn't be huge issue. It is strange that you don't have more "likes" because you are using materials that are available for everyone but plus you do practice stuff which gives more interest. That book from your PBI PP videos I also got last year, it is amazing stuff. And I always love to watch other experienced users/tutors for many other tricks or approaches for solving problem. Put some promotion man (others doing it a loooot) =) would be pity that your videos remain in "shadow".
Those are some pretty good tips, paricularly how you sorted out the name string. Unfortunately, the raw data does not always allow for such a straightforwand approach. You may have a few people with 2 middle initials, some academic titles, some Spanish people with 2 last names, all that great fun.
For the initial, mid(c2,find(" ",c2,1),3) works.
Hi. To get last name I think that would be easier =RIGHT(C2;LEN(C2)-SEARCH(".";C2)-1)
Possible alternatives to the VALUE(G2) solution for the telephone number format can also be =G2*1 or the unary operator, =--G2 (two hyphens).
Great Stuff!Some Really Helpful Tips..Thank You Craig
It was lengthy lol, but hopefully there is some extra value in it too.
7:50 when you use the PROPER function the S's after the apostrophes are in caps. Is there a way to fix that?
21:00 I didn't need to subtract the SEARCH term to get the full name. You got it right even without that. That shit got too complicated anyways with the - and + in the functions...
Thanks.
Good catch, I hadn't noticed that! I don't believe that you can fix that - interesting that excel chooses to capitalize after an apostrophe.
Excellent - I always like learning more efficient ways of getting a task done.
Thanks a lot :) Your videos are so helpful to me
The middle initial formula could be =MID(C2,SEARCH(" ",C2)+1,2)
thank you. its a A+ content. Could you please let share the most useful function list in excel?
For the last name why wouldn't you use the Right function?
When I attempt to execute the flash fill while using the PROPER function (like you do at 7:40), I get an error message stating, "we looked at the data... and didn't see a pattern". So it proceeds to fill a recurring pattern depending on how many cells I manually input. E.g. B's Beverages, Island Trading, B's Beverages... Is there a way to fix this? I don't run into this issue when I just manually type out the name and excel auto-detects the pattern.
Thanks for the series. Should we still buy the book if you continue the series? Seems to obviate the need to buy the book... cheers.
I strongly recommend buying the book if you intend on attempting the certification exam. You need to be able to complete all of the practice tasks in the book without reference to the text (or my videos) in order to be confident going into the test. Additionally there will be some questions on the test that refer to concepts contained in the text but are not included in the practice tasks (and therefore not in my videos either).
Nice videos as usually.
Well, Power Query solves most of the job you did here - if that is counting as a solution.
Indeed, that's one of the many nice things about PQ! I started recording a set a videos on PP, PQ and PBI but they never really gained any traction lol.
@@CarshaltonAdvisory I started to check your videos too recently, because I planned to do this Exam for myself. I mean, I am quite into Excel and PBI and I guess this wouldn't be huge issue. It is strange that you don't have more "likes" because you are using materials that are available for everyone but plus you do practice stuff which gives more interest. That book from your PBI PP videos I also got last year, it is amazing stuff. And I always love to watch other experienced users/tutors for many other tricks or approaches for solving problem.
Put some promotion man (others doing it a loooot) =) would be pity that your videos remain in "shadow".