Updated: Sep 29, 2021
For today’s Excel tutorial, I will be showing you how to use the NUMBERVALUE Function. The NUMBERVALUE Function converts a text value to a number by specifying the decimal and group separators. In other words, we can replace a comma with a period in our value.
We will also show you how to convert your NUMBERVALUE 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/
For today’s Excel tutorial, I will be showing you how to use the NUMBERVALUE Function. The NUMBERVALUE Function converts a text value to a number by specifying the decimal and group separators. In other words, we can replace a comma with a period in our value. Without further ado, let us go through the example for the day.
How to use NUMBERVALUE Function
In our example, we are given a mess up Excel. Some of the values for team size are left align, some are right align, and some have the comma in them. As for sales, some of the values has a comma while the others have a period as their decimal points. The mess-up excel value has prevented us from finding the Sales per Person. Let us clean this up with NUMBERVALUE.
Select Cell H4, type in equal, NUMBERVALUE, open parentheses. Select Cell C4 and hit return. Next, auto-fill the formula to the remainder cells. You will notice, all the values are clean up and align right.
Next, we will use NUMBERVALUE to replace all commas with the period. Select Cell I4, type in equal, NUMBERVALUE, open parentheses. Select D4, comma and space. For the decimal separator. Open quote, type in comma, close quote and type in comma. For the group separator. Space, open quote, period, close quote. Close parentheses and hit return. Next, auto-fill the remainder cells. Awesome, now all of our sales values are align right with consistency period as their decimal points.
Lastly, let us find the total sales per person for the countries. Select Cell J4, type in equal, select on I4, type in divide, select on H4 and hit return. We will auto-fill the remainder cells. Nice, all the excel formulas are working and arranged neatly for reporting.
There you have it, the NUMBERVALUE 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 NUMBERVALUE Function into Application Programming Interface (API)
I will be using AlchemyJ Studio Excel Add-in to turn this NUMBERVALUE function Excel into an API. To do this, I will need AlchemyJ Excel installed and with a pre-set of rules. Now, 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 5000 comma 20 and select execute. Will we get 5000 period 20 and replace the comma? Excellent, we have gotten 5000 period 2. Which is correct.
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.