For today’s Excel tutorial, You will be learning the SEARCH Function. In our example, we are given a list of emails addresses. First, I will show you how to find the location of the period, without and with the start number. Second, we will learn how to filter each email's full, first, and last name. We will be applying the SEARCH function in all the examples.
We will also show you how to convert your SEARCH 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:
Article
For today’s Excel tutorial, You will be learning the SEARCH Function. In our example, we are given a list of emails addresses. First, I will show you how to find the location of the period, without and with the start number. Second, we will learn how to filter each email's full, first, and last name. We will be applying the SEARCH function in all the examples.
How to use SEARCH Function
First, let us find the period location without a start number. Select Cell C4, type in equal, SEARCH, open parentheses. Open quote, period and close quote. Type in comma and select Cell B4. Close parentheses and hit return. You will get 5. The period represents the 5th character after ELON. Let’s do a quick auto-fill and find out the remaining cell value.
Second, let us find the period location with a start number. For this, let us take 6 to start our number count and find the location of our second period. Select D4, type in equal, SEARCH, open parentheses. Open quote, period and close quote. Type in comma and select Cell B4. Type in comma and 6. Close parentheses and hit return. You will get 17. The second period is located at the 17th character after spacex. Let’s do a quick auto-fill and find out the remaining cell value.
Third, let us use the SEARCH Function to extract the Full Name from the email address. Select Cell E4, type in equal, LEFT, open parentheses. Select Cell B4, comma and space. SEARCH and open parentheses. Open quote, @, close quote. Comma and select Cell B4. Close parentheses, minus 1 and close parentheses than hit return. You will get the full name Elon.Musk. We will auto-fill the remaining cell value.
Fourth, to find the First name of Elon Musk. We will input the previous formula, but we will replace Cell B4 with Cell E4 and replace @ with period. Now, let us find out the outcome of the formula. Awesome, we have gotten Elon. As usual, we will auto-fill the remaining cell.
Lastly, we will find the last name. It will be slightly tricky but bear with me, and you will get it. Select Cell G4, type in equal, RIGHT. Open parentheses, select Cell E4 and comma. Type Len, Open parentheses, select Cell E4 and close parentheses. Type minus, SEARCH, Open parentheses. Open quote, period, close parentheses and comma. Select Cell E4 and double close parentheses, and hit return. You will get Musk, the last name of Elon. Finally, let us do a final auto-fill on our remaining cell.
Intermission
There you have it, the SEARCH 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 SEARCH Function into Application Programming Interface (API)
I will be using AlchemyJ Studio Excel Add-in to turn this SEARCH Function into an API. To do this, I will need AlchemyJ Excel installed and with a pre-set of rules. I will click on AlchemyJ Tab, select Generate API and click on Generate API. The file is now converting. Okay, your API is ready.
Next, I will select the AlchemyJ tab and launch this open API that AlchemyJ generates. I will expand this tab, and select try it out. I will input Elon.Musk@spacex.com and select execute. Awesome, we got Elon.Musk. Which is correct.
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.