Today we will be learning the SUBSTITUTE Function. The function replaces existing text with new text in a text string. In our excel spreadsheet, we will see the message “The quick brown fox jumps over the lazy brown dog”. A widely known sentence that contains all the letters of the English alphabet.
We will also show you how to convert your SUBSTITUTE function into an API at the end of the video.
Download: AlchemyJ FREE Community Edition: https://www.alchemyj.io/download
Excel Template: https://www.alchemyj.io/aj-excel-template
Learn More:
How to setup AlchemyJ: https://www.youtube.com/watch?v=eTyYTf1sw9k&feature=youtu.be&ab_channel=AlchemyJ How AlchemyJ works: https://www.youtube.com/watch?v=bAQHo8I1xuQ&ab_channel=AlchemyJ
Let's connect: LinkedIn: https://www.linkedin.com/company/alchemyj/
Article
Today we will be learning the SUBSTITUTE Function. The function replaces existing text with new text in a text string. In our excel spreadsheet, we will see the message “The quick brown fox jumps over the lazy brown dog”. A widely known sentence that contains all the letters of the English alphabet. Without further ado, let us SUBSTITUTE some texts.
How to use SUBSTITUTE Function
In our first example, we will learn how to substitute the text brown to black. Select Cell C4, type in equal, SUBSTITUTE, open parentheses. Select Cell B4, comma, space and open quotes. Type “brown” and close quotes. Next, comma, space and open quotes. Type “black”, close quotes and parentheses and hit return. You will get “The quick black fox jumps over the lazy BROWN dog”.
Unfortunately, SUBSTITUTE is case-sensitive. Thus, the upper-case "BROWN" was not substituted to black. Now, let us do an autofill. As usual, cell C5 upper-case "BROWN" was not substituted.
Not to worry, we will show you the solution to overcome the case-sensitive issue. Select Cell D4, type in equal, SUBSTITUTE, open parentheses. Type SUBSTITUTE and open parentheses. Select Cell B4, comma, space and open quotes. Type in “brown”, comma and space. Open quotes, “black”, close quotes and parentheses. Comma, space, open quotes, Type “BROWN”, close quotes, comma and space. Open quotes, “black”, close quotes and parentheses and hit return.
You will get “The quick black fox jumps over the lazy black dog”. Both upper and lower cases of brown are replaced with black. Now, let us do an autofill. Excellent, all “browns” are replaced with blacks.
Intermission
There you have it, the SUBSTITUTE function for Excel. If you have any excel formula you want us to teach, let us know in the comment section below. Don't forget to subscribe, hit the bell button and turn on the notification to have more free excel function tutorials. But, before you go, we have a bonus clip for you. I will be turning this Excel spreadsheet into an API. Stay tuned to see how the magic works.
Convert SUBSTITUTE Function into Application Programming Interface (API)
I will be using AlchemyJ Studio Excel Add-in to turn this SUBSTITUTE function Excel into an API. To do this, I will need AlchemyJ Excel installed and with a pre-set of rules. I will select AlchemyJ Tab, select Generate API and click on Generate API. The file is now converting. Okay, your API is ready.
I will now launch this open API that AlchemyJ generates. I will expand this tab and select try it out. I will input “The quick brown fox jumps over the lazy BROWN dog” with both upper and lower browns texts, and select execute. Let us check if our SUBSTITUTE function can replace all the browns texts to black. Awesome, all browns texts are replaced with black.
Closing
AlchemyJ Studio Excel Add-in turns your Excel business model into API. This way, you can integrate your Excel model into other applications. You can download AlchemyJ Studio Excel Add-in for free at alchemyj.io. And once again, thank you for watching. So which Excel function you wish to be converted into an API? Let us know in the comment section below. Lastly, don't forget to subscribe, hit the bell button and turn on the notification. See you soon.