 |
|
| TheExcelAddict.com | |
| June 25, 2020 |
|
| Hi fellow Excel Addict, | |
Last week
I finally got my hair cut after more than three months.
Just in time for the mini heat wave we have been having
here this week. Thankfully the coronavirus situation in Newfoundland is very good -- only one new case in almost two months. Travel restrictions to our neighbouring provinces, whose COVID situation is also in very good shape, are being lifted next week. Tina an I will finally be able to visit our daughter in Nova Scotia. We are so happy to be able to do this but it does feels a bit strange at a time when I see much of the rest world heading in the wrong direction with COVID. Even with Newfoundland being in excellent shape, it's comforting to see that the vast majority of people here are still not letting their guard down with regards to safety even as restrictions are being further relaxed. Please be safe. If you like my tip today, please share it on social media and with your colleagues and any other Excel users you know. And while you're at it, please recommend that they sign up for my newsletter. Have a great day and keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
| |
|
Having a positive attitude can help us in difficult times. I hope today's quote will help you foster a positive attitude today. Quote of the Day "Your regrets aren't what you did, but what you didn't do. So I take every opportunity" -– Cameron Diaz -- |
|
|
If you have a favourite quote, send it to me
and I may post it in my newsletter.
|
|
THIS WEEK'S 'EXCEL IN MINUTES' TIP |
|
When You Need To SUBSTITUTE Text in Excel |
|
|
The SUBSTITUTE function is used when you need to replace specific text that occurs anywhere within a text string with some other text. For example, you may want to replace the dashes in an account number with forward slashes or maybe even with nothing (i.e. blanks). You may be thinking "But I can use Find and Replace to do that." Yes, but there may be situations where you don't want to change the original data. You may want to create new text in a separate column. The SUBSTITUTE function requires three pieces of information; a fourth is optional. SUBSTITUTE(text,
old_text, new_text, [instance_num])
text
- Required. This is a text string or cell reference
containing text in which you want to substitute
characters.
old_text - Required. This is the text or reference to a cell containing the text you want to replace. new_text - Required. This is the text or reference to a cell containing the text you want to replace it with. [instance_num] Optional. This argument specifies which occurrence of old_text you want to replace with new_text. If you specify an instance_num, only that instance of old_text is replaced. If you omit instance_num, every occurrence of old_text in text is replaced with new_text. Example 1: Description: Substitute the 2nd occurrence of the dash (-) with a forward slash (/). Formula: =SUBSTITUTE("ABC-123-456-789","-","/",2) Result: ABC-123/456-789  Example 2: Description: Substitute all occurrences of the dash (-) with forward slashes (/). Formula: =SUBSTITUTE("ABC-123-456-789","-","/") Instance_num is omitted. Result: ABC/123/456/789 
Example 3: Something else you may need to do is remove text from existing data and not replace it with anything. The SUBSTITUTE function can do that as well. In that case, you use two double quotes (e.g. "") to represent a 'blank' for the new_text argument. I used to do this at my old job when I imported account numbers from our accounting system that included dashes but I needed to use them in Excel without the dashes. Description: Substitute all occurrences of the dash (-) with blanks (""). Formula: =SUBSTITUTE("ABC-123-456-789","-","") Result: ABC123456789 
Example 4: Instead of typing text directly in the formula, you can use a cell reference to supply information to this function. So, for example, if you have a list of account numbers in column A and you want to create a new set of account numbers without the dashes in column C, you can enter =SUBSTITUTE(A2,"-","") in cell C2 and copy the formula down the column. (BONUS TIP: Finally, to convert the SUBSTITUTE formulas to Fixed Values, use my Super-Quick-Trick-to-Convert-Formulas-to-Values). 
An important point to remember about the SUBSTITUTE function is that it is CASE SENSITIVE. This is just a small taste of how useful the SUBSTITUTE function can help you in everyday applications. To really understand how this function can help you in your Excel work, I recommend that you practice using it in many different situations. As I say about all of my tips, the more you practice using them, the more new ways you will find you can apply them. |
|
|
|
|
|
|
|
 |
|
|
|
|
 |
|
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 16 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. | |