Basic Excel Function - Wildcards Function in Excel


For today’s Excel tutorial, you will be learning the Wildcard Excel Function. In our example, we are given a table of the biggest box office hits of all time. There are 2 types of Wildcards “?” representing a single character and “*” representing multiple characters.


We will also show you how to convert your Wildcard Excel 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


For today’s Excel tutorial, you will be learning the Wildcard Excel Function. In our example, we are given a table of the biggest box office hits of all time. There are 2 types of Wildcards “?” representing a single character and “*” representing multiple characters. Alright. Without further ado, let us get started.


How to use Wildcard Function

In our first example, we will be using Excel “Find and Replace” to change the short form of Sci-fi to Science Fiction. but unfortunately, we forgotten the spelling for sci-fi. In this case, we will use the “?” wild card to represent the single character.


We can do this by launching the Find and Replace by selecting Control + F on Windows and Command + F on a Mac. In the Find what: Type in sc?-fi and select Find Next. You will notice that Excel had detected Cell D4 has Sci-fi, which is correct. Select on the replace tab and in the replace with: type in Science Fiction and select replace. Excellent, Cell D4 is replaced. Lastly, we will choose Find All and select Replace All. Great, all of Sci-fi are replaced with Science Fiction.


In our second example, we will use “*” wildcard to find out the Box Office of a selected movie. “*” represents multiple characters. This function is handy when we have forgotten the spelling of a movie. Let us try it out. Select Cell G11 and type in Avatar. Select Cell H11, type in equal, VLOOKUP, open parentheses. Select on G11, ampersand, open quote, asterisk and close quote. Comma and space. Move your cursor and select the range from Cell B4 to C20. Comma and space and type in 2. Comma and space and type in False. Close parentheses and hit return.


Now, let us work on some examples. First, we will try Titanic. Excellent, the value is correct. Next, we type Ava and see if we can get the result of Avatar. The value matched. How about Ti for Titanic? Nice, it matched perfectly as well.

Intermission


There you have it, the Wildcard 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 Wildcard Function into Application Programming Interface (API)


I will be using AlchemyJ Studio Excel Add-in to turn this Wildcard 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 Avengers, 2000. Avatar, 5000. And Titanic 3000 and select execute. We will get 3000. I will replace the position of Titanic and change 3000 to 2000 and select execute. Awesome, we got 2000. Lastly, We will replace Titanic with Ti, and for the box office, we will use 5000 and see if our wildcard is working and select execute. Awesome, we got 5000. The wildcard function works perfectly.


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.



131 views