Convert Currency To Text In VBA

You are probably aware that Excel has amazing options for formatting data. We can present what we want in a lot of ways.

In certain instances, it is easier not to use VBA to automate our work, as it may seem like an overhead. But if we have a large set of data, it is always useful to find a way to manipulate it easier.

In the example below, we will show how can you convert the currency to text in Excel.

Convert Currency to Text

For this conversion, we need to do it in VBA. There is no way around it. We will input the revenue numbers in our worksheet for different years:

To make the conversion, we need to create our own formula. This can be done in VBA. To access the VBA, we need to click ALT + F11 on our keyboard, and then go to the Insert tab on the window that appears and choose Module:

Graphical user interface, text, application

Description automatically generated

We need to create several formulas to cover every aspect of the numbers. The first number in our list, in written form, is one hundred million four hundred eight thousand nine hundred sixty-eight dollars.

From this text, it is clear that we need several things:

  • Add text for numbers from one to nine
  • Add text for twenty to ninety (for the last two numbers)
  • Add text for ten to nineteen
  • Add text to thousands, millions, and dollars

Convert One-digit Numbers

For the first thing, we will create a simple function that will resolve number 1) on our list. The function will be:

This function calls for Select Case, and it basically recognizes the number from 1 to 9 in our cell and basically returns this number in letters.

We will write the number 8 in cell D2, and then insert our Digit Function in cell E2. Our function needs just one parameter- one cell. We will select cell D2 as a parameter and will get the number written as:

A picture containing text

Description automatically generated

We could have simply added a dollar after every number in our formula, and then convert a number in column D to be the currency, but we will do this later in the code.

This is what our formula looks like in the VBA:

Graphical user interface, text, application

Description automatically generated

Convert Two-digit Numbers

For the next thing, we need to cover two-digit numbers. This is the code that we will insert in our VBA:

This function first declares the variable Result, and then we set it to blank. A function has only one parameter- p.

Then we need to check our number and see if it has the number 1 on the left side. If it has, it will give us numbers from 10 to 19.

If not, it will give us the number from 20 to 90. After that, it will call for our Digit function, and write down the last number. We will our function in cells G2 and H2. This is the result that we will get:

Chart, waterfall chart

Description automatically generated

It is clear that we get the written form of the number located in cell G2 in cell H2 after we insert our function. This is what our function looks like in VBA:

Graphical user interface, text, application

Description automatically generated

Converting Currency To Text

We need to add an additional function to round up everything we did so far and to enable us to “translate” larger numbers into text, and we also need to add the word for the currency we use. In our case, we will use dollars.

This is what our final function looks like:

First thing first, we name our function, as we did in previous examples. Our function will be called Conversion, and it will have only one variable, which will be called a number.

Then we declare five variables, one called Dollars, a as a variant, and j, t, and k as integer (we could have used long for a variable type, but it takes more memory space).

We set variable a to be either nothing or thousands, or millions, depending on the number we have.

For the next step, we use TRIM for our number variable, as it removes spaces from the string except for the single spaces between words.

The most important part of our formula is:

We set variable j to be 1. Then we insert Do While Loop which will loop all the numbers in our cell. We set the t variable first to be blank, and the k variable to be equal to the last three digits of our number (from the right side). With this step, we make sure to look at hundreds in our number.

Next part of the code:

Makes sure that we track if we have hundreds in our number and then it adds the word “Hundred” to it. Else, it calls for our Tens function (if we have two digits) or Digits function (if we have one digit).

The final part of the code:

Finds if we have values over hundreds, calls for our array (thousands and millions), and inserts that value along with dollars.

Then it just skips to the next number in the cell (j=j+1).

For the last part of the code, we add dollars to our sentence.

To make this work now, all we need to do is insert the function Conversion in cell C2 and then drag the formula to the end of our list. These are the results we get:

Graphical user interface, text

Description automatically generated

Posted in vba