If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what topics you want me to cover in future videos.
Hi Sumit, I just completed your videos series on power query and found it useful. The way you explain I get it in one go. Please upload a series on power pivot as well.
Sir I have a Question form you because you are the doctor of excel and you are the only person which can give me a better answer sir I want to become a Master of excel but the technology is changes day by day and new software are come in the market so what you thank about the scope of excel in the future as it future is still bright ?
Hi Rushali.. after watching Sumit's video, I saw your question. If I understand it correctly, you can use the LOWER() function. Say you have the words: "UPPER CASE WORDS" in cell A1. In cell B1, type the formula: =LOWER(A1) and in cell B1 you will then see: "upper case words". So, the LOWER() function converts all letters in the string in the range you specify to be shown as lower case. If you want to make it permanent, you can copy and paste values over the formula in B1. I hope this helps solve your problem. Good luck!
Hai Sir, This is Arun. I have found most of the videos of your channel is very useful in the normal course of working. Thank you for all that. In addition to this I searched number of websites for Microsoft HTML object library explained in automating web page based works. But I have ended with getting Selenium object library based course for automating. I don't want everything possible through automation but just basic things. I want to enroll for exactly HTML object library VBA course. I believe that you will help me on find a suitable course / Book or resource for me. Thank you.
I have made all my courses free and uploaded to TH-cam. I am at a position where I can afford to not sell and still make a living. So released the courses for free as I thought a lot of people can benefit.
May I ask something. I set multiple categories (blue, red, green) together with some text (blue shirt, belt red, largegreenpants). My problem is I want to Identify on what categories they belong and show them on a separate column. Thanks.
Thank you so much sir, this is great. ... Sir is there any way to remove any of special character, sign, symbol,single or multiple times, only leaving alphabet and numeric value in a cell ??.... sometimes we got data from other server , that contains that kind of special character, and we want only the alpha numeric values only........ pls if you solve and make a turorial on that we will be thankful...thanks
Hi Mohammed.. after watching Sumit's video, I saw your question above. Assuming you want a way to strip out of a text string, anything but upper and lower case letters, numbers, and spaces, the following User Defined Function would do it for you: Function TextAndNumbersOnly(rng) As String Dim i As Long For i = 1 To Len(rng) If Asc(Mid(rng, i, 1)) = 32 Or Asc(Mid(rng, i, 1)) > 47 And Asc(Mid(rng, i, 1)) < 58 Or Asc(Mid(rng, i, 1)) > 64 And Asc(Mid(rng, i, 1)) < 91 Or Asc(Mid(rng, i, 1)) > 96 And Asc(Mid(rng, i, 1)) < 122 Then TextAndNumbersOnly = TextAndNumbersOnly & Mid(rng, i, 1) End If Next i TextAndNumbersOnly = TextAndNumbersOnly End Function Put the code in a module in your workbook and then you can use the function like any other EXCEL function.. type =TextAndNumbersOnly(A1) and the function will return the text in A1 stripped of anything but letters (upper and lower), numbers (0, 1, 2, 3, etc) and spaces. Assuming you have all the raw text in column A, you could enter the UDF in column B, copy down and then copy and paste values on column B, giving you a "cleaned" column of data in column B. I hope this helps.. was fun to create this UDF to solve your problem. Good luck!
Sir I have a small query regarding countifs when I have same value one column like 3000, 3000.5 , 3100 what I need the countifs subtract and differ the value above 100 how to pls suggest me
21-12-2019, Rs.1,000 by Cash, balance will be paid on 20 dec - how to remove comma from only the numbers here....so that i can use the text to column feature.
If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what topics you want me to cover in future videos.
Hello
I want to know about how to copy a set of formulas and paste them together into different ranges of cell
thank you Sumit, your tutorial is very enlightening.
Your way of presentation is too good. I am a serial follower of your all the excel videos. God bless you and keep up the good work
I am learning so much from you brother. God bless you. Love from Nepal
Thank you so much. This was really helpful.
Hi Sumit, I just completed your videos series on power query and found it useful. The way you explain I get it in one go. Please upload a series on power pivot as well.
Thank you very much, Sumit! Really useful.
Thank you for the "numbervalue" formula.
Was aware of the number formating but not the above said formula.
Yes it is useful. Thank you so much
Hi Sumit.. great tips. Thanks for sharing :)) Thumbs up!!
You are always awesome
Thanks for sharing these ideas.
May you receive the benefits of it 🙏.
thanks again for informative video👍
Great Video Very useful God Bless u
Always learning, thank you.
RIGHT
Thank u .. was very helpful
Explained nicely , thank u Sir
You just saved me hours. Thank you!
This is really useful... Thanks 🙏👍
Thanks sumit.... Looking for VBA seriers like these Excel tips.... Thanks....for such deep knowledge..
Very insightful and useful. Thanks for sharing
Nice video useful in daily work, please uploads this kinds of content frequently, thank you.
Great video ..thanks for sharing.
Thank you so much.. I've actually wanted to reject this work. Until I found this video.. Really appreciate
Thanks. Really helpful
Never knew SUBSTITUTE existed but now I'll be using it now
THANKS NICE AND HELPFUL
Super thanks to quick answer
Very useful tricks Thanks. : )
Thank you sir👍
Thanks a ton brother!
Will you please make video on transfer specific file from one folder to another folder
Please make video on power pivot.
Sir I have a Question form you because you are the doctor of excel and you are the only person which can give me a better answer
sir I want to become a Master of excel but the technology is changes day by day and new software are come in the market so what you thank about the scope of excel in the future as it future is still bright ?
You are awesome.Earlier I did by using delimited function,which consume little more time. Thanks
Glad you found the video useful Navneet :)
Thank you!
Really helpful thank you. Hit all the issues I was having in
Thanks for this.
Thanks so much sir :)
You are hero !!!!
thankyou. can you please share how to change upper case to lower case in cells? for changing capital letter to small letters?
Hi Rushali.. after watching Sumit's video, I saw your question. If I understand it correctly, you can use the LOWER() function. Say you have the words: "UPPER CASE WORDS" in cell A1. In cell B1, type the formula: =LOWER(A1) and in cell B1 you will then see: "upper case words". So, the LOWER() function converts all letters in the string in the range you specify to be shown as lower case. If you want to make it permanent, you can copy and paste values over the formula in B1. I hope this helps solve your problem. Good luck!
@@wayneedmondson1065 thank-you so much. I'll try out soon!
@@TheMoonLitGirl Glad to help :)) Good luck!
Well done!!!
Hai Sir,
This is Arun. I have found most of the videos of your channel is very useful in the normal course of working. Thank you for all that.
In addition to this I searched number of websites for Microsoft HTML object library explained in automating web page based works. But I have ended with getting Selenium object library based course for automating.
I don't want everything possible through automation but just basic things. I want to enroll for exactly HTML object library VBA course.
I believe that you will help me on find a suitable course / Book or resource for me.
Thank you.
very helpful
Hi Sumit. Why are you not selling online courses anymore?
I have made all my courses free and uploaded to TH-cam. I am at a position where I can afford to not sell and still make a living. So released the courses for free as I thought a lot of people can benefit.
@@trumpexcel Great dude. Thanks for sharing knowledge for free
Sir how to remove coma this number 345,577.45 in Excel sheet awaiting for your reply.
Did none of the methods covered in the video work for you? The first step would be to determine if it's numeric or comma has been entered manually.
May I ask something. I set multiple categories (blue, red, green) together with some text (blue shirt, belt red, largegreenpants). My problem is I want to Identify on what categories they belong and show them on a separate column. Thanks.
Thank you so much sir, this is great. ... Sir is there any way to remove any of special character, sign, symbol,single or multiple times, only leaving alphabet and numeric value in a cell ??.... sometimes we got data from other server , that contains that kind of special character, and we want only the alpha numeric values only........ pls if you solve and make a turorial on that we will be thankful...thanks
Hi Mohammed.. after watching Sumit's video, I saw your question above. Assuming you want a way to strip out of a text string, anything but upper and lower case letters, numbers, and spaces, the following User Defined Function would do it for you:
Function TextAndNumbersOnly(rng) As String
Dim i As Long
For i = 1 To Len(rng)
If Asc(Mid(rng, i, 1)) = 32 Or Asc(Mid(rng, i, 1)) > 47 And Asc(Mid(rng, i, 1)) < 58 Or Asc(Mid(rng, i, 1)) > 64 And Asc(Mid(rng, i, 1)) < 91 Or Asc(Mid(rng, i, 1)) > 96 And Asc(Mid(rng, i, 1)) < 122 Then
TextAndNumbersOnly = TextAndNumbersOnly & Mid(rng, i, 1)
End If
Next i
TextAndNumbersOnly = TextAndNumbersOnly
End Function
Put the code in a module in your workbook and then you can use the function like any other EXCEL function.. type =TextAndNumbersOnly(A1) and the function will return the text in A1 stripped of anything but letters (upper and lower), numbers (0, 1, 2, 3, etc) and spaces. Assuming you have all the raw text in column A, you could enter the UDF in column B, copy down and then copy and paste values on column B, giving you a "cleaned" column of data in column B. I hope this helps.. was fun to create this UDF to solve your problem. Good luck!
Have you ever considered attaching a transcript of the videos you make so people would have a hard copy for future reference?
Sir I have a small query regarding countifs when I have same value one column like 3000, 3000.5 , 3100 what I need the countifs subtract and differ the value above 100 how to pls suggest me
Thankyou bhai
Hi, in my excel default comma problem. When I am typing number in excel sheet automatically comma coming. How to fix it. Plz help
TQ an d I need two name one coloum .Change to next row one by one example Ram Kumar change Ram
Kumar what the formual
how to add commas in word number automatically?????
thank u
21-12-2019, Rs.1,000 by Cash, balance will be paid on 20 dec - how to remove comma from only the numbers here....so that i can use the text to column feature.
When I type = i m not finding numbervalue function
Could you give your contacy
Very Useful trick. Thumbs up. =Substitute("Sumit", "Excelled Sumit") :-)
Glad you liked the video :)
Forgot about find and replace lol. I am now starting to work with CSVs and people just put commas wherever they want making my life 100 times harder.
As per this formula it's coming Name in end. Wrong method
No sir its not working
How to remove first.
For example- yuva.1234.krishna
👍
Thank you, it was helpful
Really helpful 🙏
Thank you... 😇