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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Function Digit(dig) Select Case Val(dig) Case 1: Digit = "One" Case 2: Digit = "Two" Case 3: Digit = "Three" Case 4: Digit = "Four" Case 5: Digit = "Five" Case 6: Digit = "Six" Case 7: Digit = "Seven" Case 8: Digit = "Eight" Case 9: Digit = "Nine" Case Else: Digit = "" End Select End Function |
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:
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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
Function Tens(p) Dim Result As String Result = "" If Val(Left(p, 1)) = 1 Then Select Case Val(p) Case 10: Result = "Ten" Case 11: Result = "Eleven" Case 12: Result = "Twelve" Case 13: Result = "Thirteen" Case 14: Result = "Fourteen" Case 15: Result = "Fifteen" Case 16: Result = "Sixteen" Case 17: Result = "Seventeen" Case 18: Result = "Eighteen" Case 19: Result = "Nineteen" Case Else End Select Else Select Case Val(Left(p, 1)) Case 2: Result = "Twenty " Case 3: Result = "Thirty " Case 4: Result = "Forty " Case 5: Result = "Fifty " Case 6: Result = "Sixty " Case 7: Result = "Seventy " Case 8: Result = "Eighty " Case 9: Result = "Ninety " Case Else End Select Result = Result & Digit(Right(p, 1)) End If Tens = Result End Function |
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:
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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
Function Conversion(ByVal number) Dim Dollars Dim a As Variant Dim j, t, k As Integer a = Array("", "", " Thousand ", " Million ") number = Trim(Str(number)) j = 1 Do While number <> "" t = "" k = Right(number, 3) If Val(k) <> 0 Then k = Right("000" & k, 3) If Mid(k, 1, 1) <> "0" Then t = Digit(Mid(k, 1, 1)) & " Hundred " End If If Mid(k, 2, 1) <> "0" Then t = t & Tens(Mid(k, 2)) Else t = t & Digit(Mid(k, 3)) End If End If If t <> "" Then Dollars = t & a(j) & Dollars End If If Len(number) > 3 Then number = Left(number, Len(number) - 3) Else number = "" End If j = j + 1 Loop Select Case Dollars Case "" Dollars = "No Dollars" Case "One" Dollars = "One Dollar" Case Else Dollars = Dollars & " Dollars" End Select Conversion = Dollars End Function |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
j = 1 Do While number <> "" t = "" k = Right(number, 3) If Val(k) <> 0 Then k = Right("000" & k, 3) If Mid(k, 1, 1) <> "0" Then t = Digit(Mid(k, 1, 1)) & " Hundred " End If If Mid(k, 2, 1) <> "0" Then t = t & Tens(Mid(k, 2)) Else t = t & Digit(Mid(k, 3)) End If End If If t <> "" Then Dollars = t & a(j) & Dollars End If If Len(number) > 3 Then number = Left(number, Len(number) - 3) Else number = "" End If j = j + 1 Loop |
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:
|
1 2 3 4 5 6 7 8 |
If Mid(k, 1, 1) <> "0" Then t = Digit(Mid(k, 1, 1)) & " Hundred " End If If Mid(k, 2, 1) <> "0" Then t = t & Tens(Mid(k, 2)) Else t = t & Digit(Mid(k, 3)) End If |
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:
|
1 2 3 4 5 6 7 8 9 |
If t <> "" Then Dollars = t & a(j) & Dollars End If If Len(number) > 3 Then number = Left(number, Len(number) - 3) Else number = "" End If j = j + 1 |
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:
