NumName: Excel VBA Function to Convert Huge Long Numbers into their Numeric Word Names in a Text String Format

Mark Main

New Member
Joined
May 11, 2011
Messages
5
I made an Excel function that converts a large string of numbers into their numeric word names. I am offering it to the community as free public domain.

It has many parameters to control the desired output. You can choose USA or European names with a simple parameter change. English names are used, however I designed this so that you can easily translate the English word to any other language--if anyone does this, send me a copy and I'll keep the code on my sites for others to use.

Here is a link to the workbook that includes the worksheet example, instructions of use, an a list of the official numeric names as a reference:

http://www.markmain.co/public/NumName.xlsm

The macro is found using Alt-F11 (after clicking the Enable macro button). Here are the instructions that I made for my macro:

Function NumName
by Mark D Main
version 1.0
Free for public domain use
Provided freely without any expressed warrenty--you assume all responsibility in using it.

Optional numType As Byte = 0, _
Optional decimalStr As String = ".", _
Optional currencyName As String, _
Optional oneCurrencyName As String, _
Optional fractionName As String, _
Optional oneFractionName As String, _
Optional fractionSize As Integer = -1, _
Optional fractionZeros As String = "x", _
Optional grammerType As Byte = 3) As String​

numText:
String; required field
The numric text of digits that will be translated by the function.
The function will delete all non-numeric characters and concatinate what is left into one long number; all currency symbols are removed and ignored. The only remaining characters will be digits and a single decimal point and/or minus sign (dash) to denote a negative number; this dash must be the first or last character used (excluding any leading or trailing spaces, which are automatically removed.​

numType:
0 or 1;
0 will use the USA names and 1 will use the European names (see "names" tab).​

decimalStr:
String; optional field
Typically a period or comma is used, but it can be represented by any character(s) string of any length.​

currencyName:
String; optional field
This curency name text is inserted between the whole numbers and the decimal fraction--just before the word "and" that connects these two pieces. It is best to enter this as lowercase and control it's capitlization with the grammerType parameter; however capitalizing here will force capitalization for grammerType values of 0 through 3. Blank turn this feature off. E.g. "dollars", "pounds"​

oneCurrencyName:
String; optional field
used for just 1 unit; e.g. one dollar. Will us currencyName if left blank.​

fractionName:
String; optional field
If used then this fractional curency name will be used on the far right of the text following the display of fraction presented in whole numbers; otherwise, if left blank, the fraction is simply displayed numerically.
Note: if a fraction name is used here then you must also provide a fractionSize > 0.​

oneFractionName:
String; optional field
used for just 1 unit; e.g. one cent. Will us fractionName if left blank.
See "Note" in fractionName above.​

fractionSize:
-1, 0, 1 or more; optional field
-1 = variable decimal size--display is based on the number of decimals provided
0 = fixed size of zero decimal places; no decimals are displayed; all decimal numbers are truncated--no rounding is performed
1 or more = fixed decimal places--the number declares the size; trailing zeros are padded if needed; any excess numbers are truncated--no rounding is performed; e.g. "2" would be used for "cents" because 1 cent uses 2 decimal places​

fractionZeros:
x, 0, z, n, nn or 0n; optional field
x = (default) only display fractions not equal to zero
0 = display all fractions with zero shown as "0"
0n = display all fractions with zero shown as "0"; and zero currency shown as "no"
z = display all fractions with zero shown as "zero"
n = display all fractions with zero shown as "no"
nn = display all fractions with zero shown as "no"; and zero currency shown as "no"
adding an asterisk "*" before or after any of these choices (e.g. x* or *nn) will display the decimals as sized to the fractionSize by padding leading zeros.​

grammerType:
0, 1, 2, 3, 4 or 5; optional field
0 = no grammer correction; lowercase "and"
1 = add commas between each 3-digit set; lowercase "and"
2 = add commas and hyphenate ten digit words; lowercase "and"
3 = (default) add commas and hyphenate ten digit words and uppercase first letter; lowercase "and"
4 = add commas and hyphenate ten digit words and uppercase first letter; uppercase "And"
5 = add commas and hyphenate ten digit words and uppercase for all letter

EXAMPLES:

This shows the text input and output using some parameters to produce typical dollars an cents output:

"00000" --> "Zero Dollars and No Cents"

"0" --> "Zero Dollars and No Cents"

"0.00" --> "Zero Dollars and No Cents"

"0000.1" --> "Zero Dollars and 10 Cents"

"0000.0123" --> "Zero Dollars and 01 Cent"

"000.023456789" --> "Zero Dollars and 02 Cents"

"000120" --> "One Hundred Twenty Dollars and No Cents"

"100" --> "One Hundred Dollars and No Cents"

"10" --> "Ten Dollars and No Cents"

"1" --> "One Dollar and No Cents"

"0100" --> "One Hundred Dollars and No Cents"

"1000000" --> "One Million Dollars and No Cents"

"159487326159487326159487326159487732615948732615974873261594873261594873261594873261594815948732615964873269798615948732615948732615948732615948732615948732615948732615948732615948732615948732615948159487326159487326159487326159487326159487326159487326159487326159487326159487326159487326159487326159487326.69" --> "One Hundred Fifty-Nine Centillion, Four Hundred Eighty-Seven Novemnonagintillion, Three Hundred Twenty-Six Octononagintillion, One Hundred Fifty-Nine Septnonagintillion, Four Hundred Eighty-Seven Sexnonagintillion, Three Hundred Twenty-Six Quinnonagintillion, One Hundred Fifty-Nine Quattuornonagintillion, Four Hundred Eighty-Seven Trenonagintillion, Three Hundred Twenty-Six Duononagintillion, One Hundred Fifty-Nine Unnonagintillion, Four Hundred Eighty-Seven Nonagintillion, Seven Hundred Thirty-Two Novemoctogintillion, Six Hundred Fifteen Octooctogintillion, Nine Hundred Fourty-Eight Septoctogintillion, Seven Hundred Thirty-Two Sexoctogintillion, Six Hundred Fifteen Quinoctogintillion, Nine Hundred Seventy-Four Quattuoroctogintillion, Eight Hundred Seventy-Three Treoctogintillion, Two Hundred Sixty-One Duooctogintillion, Five Hundred Ninety-Four Unoctogintillion, Eight Hundred Seventy-Three Octogintillion, Two Hundred Sixty-One Novemseptuagintillion, Five Hundred Ninety-Four Octoseptuagintillion, Eight Hundred Seventy-Three Septseptuagintillion, Two Hundred Sixty-One Sexseptuagintillion, Five Hundred Ninety-Four Quinseptuagintillion, Eight Hundred Seventy-Three Quattuorseptuagintillion, Two Hundred Sixty-One Treseptuagintillion, Five Hundred Ninety-Four Duoseptuagintillion, Eight Hundred Fifteen Unseptuagintillion, Nine Hundred Fourty-Eight Septuagintillion, Seven Hundred Thirty-Two Novemsexagintillion, Six Hundred Fifteen Octosexagintillion, Nine Hundred Sixty-Four Septsexagintillion, Eight Hundred Seventy-Three Sexsexagintillion, Two Hundred Sixty-Nine Quinsexagintillion, Seven Hundred Ninety-Eight Quattuorsexagintillion, Six Hundred Fifteen Tresexagintillion, Nine Hundred Fourty-Eight Duosexagintillion, Seven Hundred Thirty-Two Unsexagintillion, Six Hundred Fifteen Sexagintillion, Nine Hundred Fourty-Eight Novemquinquagintillion, Seven Hundred Thirty-Two Octoquinquagintillion, Six Hundred Fifteen Septquinquagintillion, Nine Hundred Fourty-Eight Sexquinquagintillion, Seven Hundred Thirty-Two Quinquinquagintillion, Six Hundred Fifteen Quattuorquinquagintillion, Nine Hundred Fourty-Eight Trequinquagintillion, Seven Hundred Thirty-Two Duoquinquagintillion, Six Hundred Fifteen Unquinquagintillion, Nine Hundred Fourty-Eight Quinquagintillion, Seven Hundred Thirty-Two Novemquardragintillion, Six Hundred Fifteen Octoquardragintillion, Nine Hundred Fourty-Eight Septquardragintillion, Seven Hundred Thirty-Two Sexquardragintillion, Six Hundred Fifteen Quinquardragintillion, Nine Hundred Fourty-Eight Quattuorquardragintillion, Seven Hundred Thirty-Two Trequardragintillion, Six Hundred Fifteen Duoquardragintillion, Nine Hundred Fourty-Eight Unquardragintillion, Seven Hundred Thirty-Two Quardragintillion, Six Hundred Fifteen Novemtrigintillion, Nine Hundred Fourty-Eight Octotrigintillion, Seven Hundred Thirty-Two Septtrigintillion, Six Hundred Fifteen Sextrigintillion, Nine Hundred Fourty-Eight Quintrigintillion, One Hundred Fifty-Nine Quattuortrigintillion, Four Hundred Eighty-Seven Tretrigintillion, Three Hundred Twenty-Six Duotrigintillion, One Hundred Fifty-Nine Untrigintillion, Four Hundred Eighty-Seven Trigintillion, Three Hundred Twenty-Six Novemvigintillion, One Hundred Fifty-Nine Octovigintillion, Four Hundred Eighty-Seven Septenvigintillion, Three Hundred Twenty-Six Sexvigintillion, One Hundred Fifty-Nine Quinvigintillion, Four Hundred Eighty-Seven Quattuorvigintillion, Three Hundred Twenty-Six Trevigintillion, One Hundred Fifty-Nine Duovigintillion, Four Hundred Eighty-Seven Unvigintillion, Three Hundred Twenty-Six Vigintillion, One Hundred Fifty-Nine Novemdecillion, Four Hundred Eighty-Seven Octodecillion, Three Hundred Twenty-Six Septendecillion, One Hundred Fifty-Nine Sexdecillion, Four Hundred Eighty-Seven Quindecillion, Three Hundred Twenty-Six Quattuordecillion, One Hundred Fifty-Nine Tredecillion, Four Hundred Eighty-Seven Duodecillion, Three Hundred Twenty-Six Undecillion, One Hundred Fifty-Nine Decillion, Four Hundred Eighty-Seven Nonillion, Three Hundred Twenty-Six Octillion, One Hundred Fifty-Nine Septillion, Four Hundred Eighty-Seven Sextillion, Three Hundred Twenty-Six Quintillion, One Hundred Fifty-Nine Quadrillion, Four Hundred Eighty-Seven Trillion, Three Hundred Twenty-Six Billion, One Hundred Fifty-Nine Million, Four Hundred Eighty-Seven Thousand, Three Hundred Twenty-Six Dollars and 69 Cents"

"1000000000000000000000000000000000000000000000.0100" --> "One Quattuordecillion Dollars and 01 Cent"
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Watch MrExcel Video

Forum statistics

Threads
1,122,995
Messages
5,599,256
Members
414,299
Latest member
thenewworld

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top