3 Advanced Examples of the SUBSTITUTE Function in Excel
HTML-код
- Опубликовано: 24 июл 2024
- In this video, we look at 3 advanced examples of the SUBSTITUTE function in Excel.
Want to see more advanced tricks?
Check out our online course - bit.ly/3CGCm3M
This brilliant Excel formula helps us to manipulate and evaluate text strings in Excel.
There are many reasons to love the SUBSTITUTE function. This video narrows it down to 3 advanced examples.
Here are the timings of the video.
00:00 - Introduction to the video
00:28 - Count how many words in a cell
05:14 - Convert decimal separator
09:13 - Return characters after last delimiter
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 
Хобби
Please never delete this video! It's a pearl.
Thank you 😊 I promise
Love it. Just a few days ago I was extracting the file names from full paths and wrote some convoluted VBA to achieve it. This is much more clean and concise. Thanks!
You're welcome Russ, thank you.
That's exactly what I was looking for in order to replace the first occurrence of a string with another one.
Thanks a lot!
Amazing. Computergaga is one Word, but it means really a lot to everyone who wants to be good at Excel. Your Excel knowledge is excellent Alan. Thanks indeed.
Thank you Salim.
Thank you so much Computergaga for such amazing content.
You're welcome Pratyush. Thank you.
Thanks so much for sharing your wealth of knowledge! Great video!
You're welcome Sandy.
Excellent video depicting example of substitute function. You are showing the usefulness of this formula by demonstrating scenario. thanks a lot.
You are welcome! Thank you for your comments.
Your channel has been a life saver again and again. Much more useful than attempting to google random functions.
Great to hear, Nick. Thank you.
Great video. Thank you very much; example two was exactly what I needed.
Awesome! Thank you, Oostenwald.
great video sir, very good use of substitute formula. thanks sir
You're welcome. Thank you, Deepak.
Wonderful ！！👍👍
thanks so much, really useful!
Great to hear! Thank you, Yaroslav.
Thank you.
You're welcome Vu Hoang.
This was GOOD information! Tighten up the length a little bit.
Thank you, John.
Totally totally awesome !!!
Thanks 😊
Brilliant 👏
Thank you, George.
The way you break down and explain each and every step is amazing! The issue I am currently having is perhaps related to, but different than the 3 examples you showed. I want to add zeros to a text string of characters. The 6 numerical characters are broken into 3 pairs which are then separated by 2 hyphens. For example: 01-01-22.
I wish to insert a zero to the right of each hyphen. Example: 01-001-022. I have tried various methods but to no avail. Any suggestions?
Thank you, Mike 👍 For your issues, this formula will work - =SUBSTITUTE(A1,"-","-0")
It assumes that your values begin in cell A1.
@@Computergaga Wow, that did the trick! Thank you so much! I'm giving you a 'social distance' handshake!
👊
Good job 👏🏻
Thank you 😊
@computergaga Wondering if Find and Replace, Text to Columns would have made this simpler...
Can do. I'm not sure what example(s) you're referring too. But they are both underestimated tools, that are worth gold to an Excel user.
If i want mid insted of right, what will be the formula? Is this correct
=MID(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUE(A2," ","")))
What is the formula for middle name?
Thanx
👍
To be honest no words for u sir
Words are not enough to compliment u
Your words are enough. Thank you very much Khadim.
@@Computergaga sir how can i be a master in making dashboard?
please show me , how to set next days (find next day cross sheet) in excel?
Sorry Mao, I'm not entirely sure if I understand. If the date was in cell A2. Next day would be =A2+1.
@@Computergaga example : cross sheet 1 and sheet 2 , but we no need holiday . ( i find date , want to run day auto )
Genius
Thank you, Paul.
W video from a W man
😊
Plz give me a formula to extract text from numbers
I have the following video here that splits text and numbers - ruclips.net/video/kKlw6S36TF4/видео.html
A more complex example may require a macro.
In MS-Excel, How can I Change as follows (automatically)-
1 into 6, 2 into 7, 3 into 8, 4 into 9, 5 into 0, 6 into 1, 7 into 2, 8 into 3, 9 into 4, 0 into 5.
As I type 248 in a cell, I would get 793 as result in another cell,
As I type 806 in a cell, I would get 351 as result in another cell,
As I type 100 in a cell, I would get 655 as result in another cell.
For automatic, a formula is possibly best with a lookup table to know what number to change to what. Then concatenate them.
Otherwise, a macro, but you would need to know how to create that macro.
=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",100)),100))
I find it hard figuring out where is the word "spices" he mean..until i realize it was spaces
😂
W=41CM (16.14") L=55CM (21.65") F=8CM (3.15")
Pls remove brackets & Inside Brackets data.....
Final look should be like this....
W=41CM L=55CM F=8CM