If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos. Also, I have made all of my Excel courses available for free. You can check these out using the below links: ✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/ ✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course ✅ Free VBA course - bit.ly/excel-vba-course ✅ Free Power Query Course - bit.ly/power-query-course
My hero. Hit like 5 other sites trying to do a special split. Lots of crazy over complicated foruimlas that wouldn't work for me. This guy, this guy gets it! THANK YOU! it took me 20 seconds to teach flash fill and bam, my list is spit.
Wow great.., Sumit I just started watch your channel recently. I am very pleased to say that your explanation is so clear and your teaching method is very interesting. Really I love it. waiting for your next videos eagerly. ThanX a lot Sumit ji.
This is very useful. thank you! but how about the numbers is like " 313 .60" this, how could we seperate by the VBA? I found that the VBA ignored the . thank you
Nice one Sumit! Lots of use cases for these techniques. Thanks for demonstrating. Sometimes I have to extract numbers that are mixed with text. I use this formula: =--TEXTJOIN("",TRUE,FILTER(MID(A2,SEQUENCE(LEN(A2)),1),ISNUMBER(--MID(A2,SEQUENCE(LEN(A2)),1)))) which pulls out all the numbers from left to right as a single numerical value. Not always the desired goal, but when needed, it is useful. Requires Office 365 for dynamic array functions. Thanks for sharing your tips! Always useful and interesting :)) Thumbs up!!
Nice tip for passing multiple values via array for the find_text parameter of FIND function in the Formula approach. on my version of Excel, need to Shift Ctrl Enter to get it to work. It would be trickier if there are also more text after the Number). Is there a good Formula approach not too long also?
Very useful video, found the info very relevant . Thank you Sumitji. Could you also please clarify if we need to save this file as macro enabled file.and what if we need to apply this VBA code across all the workbooks.
I have a Sheet having all the records related to equipment's 2nd sheet i want to create a record based a user criteria with assignment (Means he will select the equipment then it will create a new row like that) In the same sheet again if user search based on the criteria he will select equipment then create new record This is near to inventory management , but inventory management not providing the history assignment. so i am expecting like this. How can I achieve this ? Could you suggest on this ? (Don`t recommend macro or vba because it is excel on web)
Lovely, as usual. But I can't get it to work when I put the code in my "personal.xlsb" and try to call the function from an Excel workbook (.xlsx). Work nice if the code is in a module of the same workbook.
When a function is saved in the Personal macro workbook, you need to use =PERSONAL.XLSB!GetNumber(A2). So before the function name, add =PERSONAL.XLSB!
can anyone tell me how to fix my .xlsx format or apps which has two menus showing up, the upper which is functional but grayed out Review Tab of which tracking option I am interested to function or study how it works. The lower menu shows several chosen icons if any one of them is clicked, the whole worksheet crashes including the rest of the worksheets currently opened.
Sir How to have monthly and cumulative data row wise instead of columns using pivot table if we have one years data as raw data. In row 1 i want to have month data and in row 2 cumulative data.
Sir, ek video banaiye Tax invoice me quantity and rate jo 12 % tax lag raha hai (above 1000 rate) to convert kare ke quantity and rate 5% me (under 1000 rate) quantity increase kar ke per taxable value same karke excel me format kese banaiye
I think I dont know how to do it lol. I have this text: "bohomodern wall minimalist; Search volume: 219, Competition: 1814". I need to extract 219 into one cell and 1814 into another one. I ned to apply it for the whole page. How do it do it? :D
when I go to split option in power query it only shows split my delimiter and by number of character. Other options are not available i.e. by digit to non digit etc. My data consist in 1 row and contain alphabets and numbers same format as yours. Kindly help!
Power query would still work as it would split the string whenever non digit changes to digit. The VBA code also works, but it would extract and give alll the numbers together (and same with text). And if the data is consistent, even flash fill can work
Hi Sumit, I have small query, please help. I have a data where A2=A3=A4, I want use a formula so that, the formula checks if below 2 rows are same and then highlights all 3 rows from left to right. The data looks something like this: 2022-07-04 13:30:00 +0 Worcester 2022-07-04 13:30:00 +0 Worcester 2022-07-04 13:30:00 +0 Worcester 2022-07-04 13:45:00 +0 Ayr 4th Jul 2022-07-04 13:45:01 +0 Ayr 4th Jul 2022-07-04 13:45:02 +0 Ayr 4th Jul I want each set of 3 rows to get a different color too (If this is not possible a color gradient would be fine too.) Please help
I have a question, Sir. Is there any way to sum numbers only that include dates on the same row? like this, the data is from A2 up to J2. A2 is the amount of money, B2 is the payment day, C2 amount paid, D2 payment day. At last, there should be the total amount paid throughout the year. So when I drag all it will sum up the date too. Is there any way to ignore the date and sum only the amount paid, Sir?
Hi All, I'm using power query to fetch data from dataverse tables into the excel sheet. But We have a different customers and customer name was filled by user manually in one of the excel column. My requirement is I need to fetch the data from dataverse table based on the customer name given user for other columns. Can someone help me on this.
How to bold part of a string in vba excel From excel sheet drop-down list,if we select how much big name it has to be bold in the letter. Example: If I select Rajith Tagore from Drop-down list Then the output should be This is to Certify that RAJITH Tagore Here Rajith Tagore should be in bold Please do the needful sir
Good afternoon Sir, How to get end date and time if i have start day and time together and to add "n" working hours excluding non working hours. example 1) if start date=02/06/2022 02:00 and to add 4:00 working hours and the working hours are from 08:00 to 18:00 and the non working hours to be excluded are of from 18:00 to 08:00 and the end date should be like 02/06/2022 12:00. what is the formula for this one sir. 2) if start date=02/06/2022 16:00 and to add 4:00 working hours and the working hours are from 08:00 to 18:00 the and the non working hours to be excluded are of from 18:00 to 08:00 and the end date should be like 03/06/2022 10:00. what is the formula for this one sir. In CCC to be completed time also same formula given sir. Can u please help me with the formula sir?
If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos.
Also, I have made all of my Excel courses available for free. You can check these out using the below links:
✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course
✅ Free VBA course - bit.ly/excel-vba-course
✅ Free Power Query Course - bit.ly/power-query-course
Sir, Please share Advance excel link ..🙏🙏
Sir,can u say about best website for excel mcqs
@TrumpExcel, what is the formula if it's "150 miles" and we want the numbers? What about the space in between? Thanks
Hi TrumpExcel, Where is your via code?
Thank you so much Sir. We found the solution that has been seeking for days.
No BS. Explanations are crystal clear. Multiple solutions proposed. 10/10 video, you get a like and a sub.
Glad you found the video useful 🙂
My hero. Hit like 5 other sites trying to do a special split. Lots of crazy over complicated foruimlas that wouldn't work for me. This guy, this guy gets it! THANK YOU! it took me 20 seconds to teach flash fill and bam, my list is spit.
One of my favourite Excel master - explanations are clear and practice file attached. Thank you so much
THANK YOU SO MUCH FOR THE FLASH FILL EXPLANATION! You have no idea how happy I am right now!
Flash Fill, OMG, that was cool. I have 69,682 rows, you saved me a lot of work!!!! Thank you. Happy New Years!
This is a a great demonstration of alternative techniques, thanks.
Thanks... Glad you found the video useful :)
Excellent treatment of these cases Sumit. They are highly requested examples by companies. Thank you very much for this great video.
Thanks Ivan... Glad you found the video useful :)
Thanks man, you saved me lots of work today
Thanks a lot Sumit, for the awesome excel tricks you shared.
Thanks... Glad you found the video useful :)
Formula worked perfectly. Thank you
Glad the video helped :)
MAGIC !!!
I have been using left right formula for the years- this is something I can use it if the pattern in cells are different -Bravo
Your channel is the BEST!!
thank you so much. This saves me a lot of time and effort
Amazing!! This saved me so much time in my new job. Thank you 🙏
Glad it helped 😊
the first technique was exactly what i needed, it did 95% of the work just gotta check a few
great as always.
formula FIND({0,1,2,3,4,5,6,7,8,9} may alternatively be expressed as FIND(SEQUENCE(,10,0) in excel 365
Thanks for sharing :)
You are awesome, I learn a lot from you.
Excellent and useful. Thanks.
Glad you found the video helpful 🙂
You are genius brother, I love your videos. Very informative, simple and to the point.👍
Thanks... Glad you found the video useful :)
you are always glamorous
Was about to type in 73 rows to seperate them! Thank you
Fantastick! Love your explanation ... Thank You ...
11 / 5.000
I point out that in my excel 2016 I must press Ctrl+Shift+Enter in order to have the result, but it works. Great!! 🙂 Thank you!!
You just save my Day .Thak you So much .
Many Thanks for this great video. You are the best in this carear.
Thank you very much saved me from spending hours to minutes 🤛
Thanks for uploading this, very helpful!
Thank you so mutch, Sir.
Thankful, hope giving more lessons about power query
Nice and clear presentation. How about reverse as numbers and letter [175MKT]... Thanks
Thankyou so much it makes my work easier
Quite good. Really enjoyed the lesson.
Thanks John... Glad you found the video useful :)
Thank you Sumit! I'm using method #2 with a formula.
Thanks for the video!
Power query is my favourite. Thank you so much
Thanks! you are the best!!!
Good info 👍 bro
Love from Karachi 🇵🇰
great job man. Thanks for the help
YOUR FILE HELPED ME
Thank You for this! Very helpful! 😊 easy to follow! ❤❤❤
Very Very Helpful Thanks alot
Thank you,video was simple and Helpful 👍
Very much useful video sir.Thank you
Excellent work
Glad you found it helpful 🙂
Very useful... Thnx for your effortful video👍👍
Wow great.., Sumit I just started watch your channel recently. I am very pleased to say that your explanation is so clear and your teaching method is very interesting. Really I love it. waiting for your next videos eagerly. ThanX a lot Sumit ji.
Thanks Siraj... Glad you're finding the videos helpful :)
Hi Sumit you're great and I love your videos😊
please amend this formula with a negative numbers and decimals too. Thank you its really useful
Excellent !!!
Well done.
LOVE IT
THANK YOU VERY MUCH
Thank you, thank you!!!! 😊
This Is very good video sir keep it up
Amazing!!! Thanks so much
Super Useful..Thanks
Welcome... Glad you found the video helpful 😊
Wow, amazing. Thanksss
you are genius 👍👍
Hi Sumit, wonderful methods, just wanted to check the VBA version whenever i enter the get text or number it shows as #NAME unable to rectify
Your videos are awesome.
Have learned a lot from them 👍
This is very useful. thank you! but how about the numbers is like " 313 .60" this, how could we seperate by the VBA? I found that the VBA ignored the . thank you
which version you are using.. i am not getting all the options in split column in power query
Got to know about this channel in Lallan top
Hi Sumit, this is awesome. Please explain how to enable power query in Excel on Mac
Thanks for guide😊
Very very good video
Nice one Sumit! Lots of use cases for these techniques. Thanks for demonstrating. Sometimes I have to extract numbers that are mixed with text. I use this formula: =--TEXTJOIN("",TRUE,FILTER(MID(A2,SEQUENCE(LEN(A2)),1),ISNUMBER(--MID(A2,SEQUENCE(LEN(A2)),1)))) which pulls out all the numbers from left to right as a single numerical value. Not always the desired goal, but when needed, it is useful. Requires Office 365 for dynamic array functions. Thanks for sharing your tips! Always useful and interesting :)) Thumbs up!!
Thanks for sharing Wayne :)
Really i enjoy this one thanks
I ask about How to make current account statement thanks
Nice tip for passing multiple values via array for the find_text parameter of FIND function in the Formula approach. on my version of Excel, need to Shift Ctrl Enter to get it to work. It would be trickier if there are also more text after the Number). Is there a good Formula approach not too long also?
Very useful video, found the info very relevant . Thank you Sumitji. Could you also please clarify if we need to save this file as macro enabled file.and what if we need to apply this VBA code across all the workbooks.
Thanks It was great
I have a Sheet having all the records related to equipment's
2nd sheet i want to create a record based a user criteria with assignment (Means he will select the equipment then it will create a new row like that)
In the same sheet again if user search based on the criteria he will select equipment then create new record
This is near to inventory management , but inventory management not providing the history assignment. so i am expecting like this.
How can I achieve this ? Could you suggest on this ? (Don`t recommend macro or vba because it is excel on web)
Lovely, as usual. But I can't get it to work when I put the code in my "personal.xlsb" and try to call the function from an Excel workbook (.xlsx). Work nice if the code is in a module of the same workbook.
When a function is saved in the Personal macro workbook, you need to use =PERSONAL.XLSB!GetNumber(A2). So before the function name, add =PERSONAL.XLSB!
Sir, could you please tell me how to save this code as custom tab in excel
Thank you!!
Nice tutorial sir
THNX A LOT.
can anyone tell me how to fix my .xlsx format or apps which has two menus showing up, the upper which is functional but grayed out Review Tab of which tracking option I am interested to function or study how it works. The lower menu shows several chosen icons if any one of them is clicked, the whole worksheet crashes including the rest of the worksheets currently opened.
Champion!!!! 😁
we need an expert in excel works, will you able to work as a free lancer for specific projects
Any idea how to do subtraction and addition between alphabets
Good one.
Thank you.
Sir
How to have monthly and cumulative data row wise instead of columns using pivot table if we have one years data as raw data.
In row 1 i want to have month data and in row 2 cumulative data.
Thank you sir.
Excellent
Sir, could you please tell me how to save this code as custom tab in excel
Sir , how to seprate chq no. With starting with '0' in separate colume
Sir, ek video banaiye Tax invoice me quantity and rate jo 12 % tax lag raha hai (above 1000 rate) to convert kare ke quantity and rate 5% me (under 1000 rate) quantity increase kar ke per taxable value same karke excel me format kese banaiye
Please make some more videos on Excel VBA
I think I dont know how to do it lol. I have this text: "bohomodern wall minimalist; Search volume: 219, Competition: 1814". I need to extract 219 into one cell and 1814 into another one. I ned to apply it for the whole page. How do it do it? :D
when I go to split option in power query it only shows split my delimiter and by number of character.
Other options are not available i.e. by digit to non digit etc. My data consist in 1 row and contain alphabets and numbers same format as yours.
Kindly help!
Thanks for the info.
I'm wondering what happens when the ID is a mix of numbers and texts, i.e. T754GE9754L00381, a serial or registration number?
Power query would still work as it would split the string whenever non digit changes to digit. The VBA code also works, but it would extract and give alll the numbers together (and same with text). And if the data is consistent, even flash fill can work
@@trumpexcel hmmm, I have to try it. Thanks again!
Hello, very informative videos that you for all of it. I have a very unusual question regarding excel. How can I communicate it to you?
Hi Sumit, I have small query, please help. I have a data where A2=A3=A4, I want use a formula so that, the formula checks if below 2 rows are same and then highlights all 3 rows from left to right. The data looks something like this:
2022-07-04 13:30:00 +0 Worcester
2022-07-04 13:30:00 +0 Worcester
2022-07-04 13:30:00 +0 Worcester
2022-07-04 13:45:00 +0 Ayr 4th Jul
2022-07-04 13:45:01 +0 Ayr 4th Jul
2022-07-04 13:45:02 +0 Ayr 4th Jul
I want each set of 3 rows to get a different color too (If this is not possible a color gradient would be fine too.)
Please help
I have a question, Sir. Is there any way to sum numbers only that include dates on the same row? like this, the data is from A2 up to J2. A2 is the amount of money, B2 is the payment day, C2 amount paid, D2 payment day. At last, there should be the total amount paid throughout the year.
So when I drag all it will sum up the date too. Is there any way to ignore the date and sum only the amount paid, Sir?
Sir , please upload that time calculation sheet tutorials by explaining briefly .
dear I have excel 2019 but Unique function is not support. kindly help
Hi All,
I'm using power query to fetch data from dataverse tables into the excel sheet. But We have a different customers and customer name was filled by user manually in one of the excel column. My requirement is I need to fetch the data from dataverse table based on the customer name given user for other columns.
Can someone help me on this.
F-1 (1.2X2.1X0.5) sir agar humme sirf bracket ke under wala number ko teen seperate column mein Lana chahta hai toh usko kese kare
How to bold part of a string in vba excel
From excel sheet drop-down list,if we select how much big name it has to be bold in the letter.
Example: If I select Rajith Tagore from Drop-down list
Then the output should be
This is to Certify that RAJITH Tagore
Here Rajith Tagore should be in bold
Please do the needful sir
Good afternoon Sir,
How to get end date and time if i have start day and time together and to add "n" working hours excluding non working hours.
example 1) if start date=02/06/2022 02:00 and to add 4:00 working hours and the working hours are from 08:00 to 18:00 and the non working hours to be excluded are of from 18:00 to 08:00 and the end date should be like 02/06/2022 12:00. what is the formula for this one sir.
2) if start date=02/06/2022 16:00 and to add 4:00 working hours and the working hours are from 08:00 to 18:00 the and the non working hours to be excluded are of from 18:00 to 08:00 and the end date should be like 03/06/2022 10:00. what is the formula for this one sir.
In CCC to be completed time also same formula given sir.
Can u please help me with the formula sir?