Have you ever wanted to spell out a number as words , perhaps for reporting / automated form filling ?

This script  / UDF  takes some cues from the various scripts out there, however they didn't cover numbers beyond a billion, decimal places beyond two, and often appended currency names to them.

If you did want to use currency names or smaller numbers, here's some links to other, similar scripts

The above is taking it to extremes, but you get the idea.

So how do we turn 123456789 into :

'ONE HUNDRED TWENTY THREE MILLION FOUR HUNDRED FIFTY SIX THOUSAND SEVEN HUNDRED EIGHTY NINE'

Excel itself has some limitations when it comes to BIG numbers.

Beyond a certain size, numbers will start being truncated / rounded.

Case in point - if you tried typing a random number of digits up to a Quadrillion (Short-Scale) or more into a cell, it'd default to scientific notation, and promptly round up to the nearest thousand .

To get around this, for big numbers, you can prefix with an apostrophe ( ' ).

This is the shortcut for telling excel to interpret the cell value as text - will allow you to enter some really big numbers, without fear of Excel rounding them off when you get beyond a trillion ( Short-Scale ) .

## Garbage in / Garbage out

Before beginning on any transformation -   we need to remove any formatting or additional data.

To cover all bases, we can process the string looking for only numbers, dashes and the first occurance of a decimal place. Beyond that we can remove the unnecessary characters.

We want to keep ASCII 48 > 57, on occurrence of 45, and one occurrence of 46.

The code :

``````

Private Function NumChunkCleanString(MyString) As String

Dim LP As Integer ' :: GENERIC LOOP VARIABLE ::

' :: RETURN CLEANSED STRING

MyString = Replace(MyString, " ", "") ' :: REMOVE SPACES
MyString = Replace(MyString, ",", "") ' :: REMOVE COMMAS
MyString = Replace(MyString, "'", "") ' :: REMOVE APOSTROPHES
MyString = StrReverse(Replace(StrReverse(MyString), ".", "|", , 1))   ' :: POINT TO PIPE FOR FIRST POINT
MyString = Replace(MyString, Chr(46), "")  ' :: NULL ALL ADDITIONAL POINTS ::
MyString = Replace(MyString, Chr(133), "")  ' :: NULL ALL ADDITIONAL POINTS ::

' :: REMOVE ANY ADDITIONAL DASHES ::
If Left(MyString, 1) = "-" And Len(MyString) > 1 Then MyString = "-" & Replace(Right(MyString, Len(MyString) - 1), "-", "")

':: REMOVE NON-NUMERIC CHARACTERS ::

For LP = 1 To Len(MyString)

If NumericDict(Asc(Mid(MyString, LP, 1))) = "Y" Then NumChunkCleanString = NumChunkCleanString & Mid(MyString, LP, 1)

Next LP

End Function

``````

This will cleanse the initial string of any extraneous characters, retaining only numbers [1-9] , a single dash on the leftmost position, and a single decimal place.

So the later functions can work off a clean number, you can see how it deals with various formats :

Hopefully this'll cover all bases.

What's the NumericDict function ?

This is just a dictionary of 'permitted' ASCII codes , that returns a 'Y' if valid.

We'll be using this functionality later, and the VBA dictionary object is a quick and easy way of performing lookups within code.

N.B If you're not already using it, you'll need a reference to the Microsoft Scripting Runtime, this will allow you to utilise data types like dictionaries that aren't in the standard Excel.

You can enable it via Tools > References > MicroSoft Scripting Runtime ( when in the VBA window ALT+F11 )

``````

Private Function NumericDict(ByVal MyInteger As Integer) As String

' :: RETURN ' Y ' OR  ' N ' IF VALID CHARACTER ::
Dim MyDict As Dictionary

Set MyDict = New Dictionary
MyDict(48) = "Y" 'ZERO
MyDict(49) = "Y" 'ONE
MyDict(50) = "Y" 'TWO
MyDict(51) = "Y" 'THREE
MyDict(52) = "Y" 'FOUR
MyDict(53) = "Y" 'FIVE
MyDict(54) = "Y" 'SIX
MyDict(55) = "Y" 'SEVEN
MyDict(56) = "Y" 'EIGHT
MyDict(57) = "Y" 'NINE
MyDict(45) = "Y" 'DASH
MyDict(46) = "Y" 'POINT
MyDict(124) = "Y" 'PIPE

If MyDict.EXISTS(MyInteger) Then

NumericDict = MyDict(MyInteger)

Else

NumericDict = "N"

End If

End Function

``````

Now that we've cleaned the extraneous data from the string, the next step is to normalise the length of it into chunks of three digits.

``````
Private Function NumChunkPadMe(ByVal MyString As String) As String

':: PAD STRING TO NEAREST MULTIPLE OF THREE ( ROUND UP!! )

End Function

``````

This will be used in the next step and simplifies the process of iterating through the numeric string and turning each 'chunk' into its written counterpart.

If we have the number  123456789 , we want to pad the string to the nearest multiple of 3.

We now have "0123456789" as the nearest multiple of 3 greater than 8 is  9.

We can then iterate through each character in the string, in reverse order, to derive the value of that portion, starting with units and working upwards :

PlaceReverseDescription
19Units
28Tens
37Hundreds
46Thousands
55Ten-Thousands
64Hundred-Thousands
73Millions
82Ten-Millions
91Hundred-Millions
10-Billions
11-Ten-Billions

The script will work through in chunks of three,  so it'd begin with "Seven Hundred Eighty Nine"

The next cluster of three, it'd return "Four Hundred Fifty Six"  and append that bracket's name, in this case 'Thousand".

For the final cluster,  it'd return "One Hundred Twenty Three" + "million" .

Assembled into one string, you'd get:

"One Hundred Twenty Three Million, Four Hundred Fifty Six Thousand, Seven Hundred Eighty Nine."

The first part of the code - this takes a 3 digit input, and returns the Hundreds, Tens and Units description.

``````

Private Function NumChunkWordBlock(MyString As String) As String

Dim LP As Index ' GENERIC LOOP
Dim NumHund, NumTen, NumUnit As Double '  Portions

NumChunkWordBlock = "" ' :: MAKE BLANK ::

NumHund = Val(Left(MyString, 1)) ' HUNDREDS
NumTen = Val(Mid(MyString, 2, 1)) ' TENS
NumUnit = Val(Right(MyString, 1)) ' UNITS

' :: DO HUNDREDS FIRST ::

If NumHund > 0 Then

NumChunkWordBlock = NumChunkName(NumHund) & " " & NumChunkName(100)

End If

':: CHECK FOR > 19

If (NumTen * 10) + NumUnit > 19 Then

NumChunkWordBlock = NumChunkWordBlock & " " & NumChunkName(NumTen * 10)

If NumUnit > 0 Then  ':: HAS UNITS?
NumChunkWordBlock = NumChunkWordBlock & " " & NumChunkName(NumUnit)
End If

End If

':: CHECK FOR <= 19

If (NumTen * 10) + NumUnit <= 19 And (NumTen * 10) + NumUnit > 0 Then

NumChunkWordBlock = NumChunkWordBlock & " " & NumChunkName((NumTen * 10) + NumUnit)

End If

End Function
``````

The next function assembles this together with its placement within the number ( Thousands, Millions, Billions etc. )

``````Private Function NumChunkWords(MyString As String) As String

Dim LP As Integer       ' :: GENERIC LOOP VARIABLE ::
Dim MyChunks As Integer ' :: CHUNKS OF 3 NUMBERS ::
Dim Outstring As String ' ::  RAW OUTPUT STRING ::
Dim PadString As String '  :: ZERO-PADDED NUMBER DIVISIBLE BY THREE ::
Dim MyPowerLUV As Double  ' :: POWER OF THREE-DIGIT PORTION ::
Dim MyPos As Integer  ' :: STRING POSITION CURSOR ::

' :: only use integer portion

MyChunks = NumChunkCount(MyString)  ' Count of three digit chunks

MyPos = 1

' :: CASE WHEN VALUE IS 0 ::
If MyChunks = 1 And PadString = "000" Then
NumChunkWords = "ZERO"
Exit Function
End If

For LP = 1 To MyChunks

MyPowerLUV = 10 ^ ((MyChunks - LP) * 3)  ' LOOKUP FOR POWER BRACKET ::

If MyPos = MyChunks Then
Outstring = Outstring & NumChunkWordBlock(Mid(PadString, (3 * LP) - 2, 3)) & " "

End If

If MyPos <> MyChunks Then
If Val(Mid(PadString, (3 * LP) - 2, 3)) > 0 Then
Outstring = Outstring & NumChunkWordBlock(Mid(PadString, (3 * LP) - 2, 3)) & " "
Outstring = Outstring & NumChunkName(MyPowerLUV) & " "
End If
End If

MyPos = MyPos + 1

Next LP

NumChunkWords = UCase(Trim(Outstring))

End Function
``````

This should be enough to handle all integers , but what about non-integer (floating point)  ?

Here's the next part - this will work forwards through the decimal portion of a string and describe that number's significance :

``````
Private Function NumChunkDecimal(MyString As String) As String

Dim LP As Integer                       ':: GENERIC LOOP VARIABLE ::
Dim LUVdouble As Double       ' ::DICTIONARY LOOKUP POWER ::

For LP = 1 To Len(MyString)

LUVdouble = 1 / (10 ^ LP)    ' RAISE 10 TO THE POWER OF LOOP ITERATION

If Mid(MyString, LP, 1) <> "0" Then ' :: ONLY RETURN DATA IF NOT ZERO AT POSITION ::
'RETURN TEXT VALUE OF POWER FROM
NumChunkDecimal = NumChunkDecimal & NumChunkName(Mid(MyString, LP, 1)) ' NUMBER NAME
NumChunkDecimal = NumChunkDecimal & " " & NumChunkName(LUVdouble)  ' POSITION NAME
If Mid(MyString, LP, 1) <> "1" Then
NumChunkDecimal = NumChunkDecimal & "S "
Else
NumChunkDecimal = NumChunkDecimal & " "
End If
Debug.Print "[" & LUVdouble & "]"
End If

Next LP
NumChunkDecimal = UCase(Trim(NumChunkDecimal)) ' :: UPPER AND TRIM ::

End Function
``````

These two functions use the following dictionary :

``````
Private Function NumChunkName(ByVal MyInteger As Double) As String

Dim MyDict As Dictionary

Set MyDict = New Dictionary

MyDict(0) = "zero"
MyDict(1) = "one"
MyDict(2) = "two"
MyDict(3) = "three"
MyDict(4) = "four"
MyDict(5) = "five"
MyDict(6) = "six"
MyDict(7) = "seven"
MyDict(8) = "eight"
MyDict(9) = "nine"
MyDict(10) = "ten"
MyDict(11) = "eleven"
MyDict(12) = "twelve"
MyDict(13) = "thirteen"
MyDict(14) = "fourteen"
MyDict(15) = "fifteen"
MyDict(16) = "sixteen"
MyDict(17) = "seventeen"
MyDict(18) = "eighteen"
MyDict(19) = "nineteen"
MyDict(20) = "twenty"
MyDict(30) = "thirty"
MyDict(40) = "forty"
MyDict(50) = "fifty"
MyDict(60) = "sixty"
MyDict(70) = "seventy"
MyDict(80) = "eighty"
MyDict(90) = "ninety"
MyDict(100) = "hundred"
MyDict(1000) = "thousand"
MyDict(1000000) = "million"
MyDict(1000000000) = "billion"
MyDict(1000000000000#) = "trillion"
MyDict(1E+18) = "quintillion"
MyDict(1E+21) = "Sextillion"
MyDict(1E+24) = "Septillion"
MyDict(1E+27) = "Octillion"
MyDict(1E+30) = "Nonillion"
MyDict(1E+33) = "Decillion"
MyDict(0.1) = "Tenth"
MyDict(0.01) = "Hundredth"
MyDict(0.001) = "Thousandth"
MyDict(0.0001) = "Ten-Thousandth"
MyDict(0.00001) = "Hundred-Thousandth"
MyDict(0.000001) = "Millionth"
MyDict(0.0000001) = "Ten-Millionth"
MyDict(0.00000001) = "Hundred-Millionth"
MyDict(0.000000001) = "Billionth"
MyDict(0.0000000001) = "Ten-Billionth"
MyDict(0.00000000001) = "Hundred-Billionth"
MyDict(0.000000000001) = "Trillionth"
MyDict(0.0000000000001) = "Ten-Trillionth"
MyDict(0.00000000000001) = "Hundreed-Trillionth"
MyDict(1E-18) = "Quitillionth"
MyDict(1E-19) = "Ten-Quitillionth"
MyDict(1E-20) = "Hundred-Quitillionth"
MyDict(1E-21) = "Sextillionth"
MyDict(1E-22) = "Ten-Sextillionth"
MyDict(1E-23) = "Hundred-Sextillionth"
MyDict(1E-24) = "Septillionth"

If MyDict.EXISTS(MyInteger) Then

NumChunkName = MyDict(MyInteger)

Else

NumChunkName = ""

End If

End Function
``````

This should cover most scenarios.

## Putting it together

Now to combine the two functions, into a single UDF to be used within a worksheet.

``````
Function NumberToWords(ByVal MyString As String, Optional IsInteger As Boolean = True) As String

Dim MyArray() As String

MyString = NumChunkCleanString(MyString)

MyArray = Split(MyString, "|")

Select Case UBound(MyArray, 1)

Case 0:
NumberToWords = NumChunkWords(MyArray(0))
Case 1:
NumberToWords = NumChunkWords(MyArray(0))
If Not IsInteger Then NumberToWords = NumberToWords & " POINT " & NumChunkDecimal(MyArray(1))
Case Else:
NumberToWords = "Cannot Calculate"
End Select

End Function
``````

This function works in three stages

1 :  Splits the string where a pipe exists into either one or two parts.

2:  Checks if the optional  'IsInteger' boolean is set, if set to False move onto

3 : Process integer and decimal portions into word strings and concatenate conditionally.

Usage in Workbook

Here's how it'd work within a sheet :

Number NumberToWords() Output Settings
123.456 ONE HUNDRED TWENTY THREE Default - only uses integer portion
123.456 ONE HUNDRED TWENTY THREE POINT FOUR TENTHS FIVE HUNDREDTHS SIX THOUSANDTHS With IsInteger=False - output now displays the decimal portion

By using the IsInteger flag, the script will add on the '.456' decimal portion.

## All scripts

Copy and paste the whole block below into a new module to use this in your own projects.

``````

Option Explicit

Function NumberToWords(ByVal MyString As String, Optional IsInteger As Boolean = True) As String

Dim MyArray() As String

MyString = NumChunkCleanString(MyString)

MyArray = Split(MyString, "|")

Select Case UBound(MyArray, 1)

Case 0:
NumberToWords = NumChunkWords(MyArray(0))
Case 1:
NumberToWords = NumChunkWords(MyArray(0))
If Not IsInteger Then NumberToWords = NumberToWords & " POINT " & NumChunkDecimal(MyArray(1))
Case Else:
NumberToWords = "Cannot Calculate"
End Select

End Function

Private Function NumChunkDecimal(MyString As String) As String

Dim LP As Integer                       ':: GENERIC LOOP VARIABLE ::
Dim LUVdouble As Double       ' ::DICTIONARY LOOKUP POWER ::

For LP = 1 To Len(MyString)

LUVdouble = 1 / (10 ^ LP)    ' RAISE 10 TO THE POWER OF LOOP ITERATION

If Mid(MyString, LP, 1) <> "0" Then ' :: ONLY RETURN DATA IF NOT ZERO AT POSITION ::
'RETURN TEXT VALUE OF POWER FROM
NumChunkDecimal = NumChunkDecimal & NumChunkName(Mid(MyString, LP, 1)) ' NUMBER NAME
NumChunkDecimal = NumChunkDecimal & " " & NumChunkName(LUVdouble)  ' POSITION NAME
If Mid(MyString, LP, 1) <> "1" Then
NumChunkDecimal = NumChunkDecimal & "S "
Else
NumChunkDecimal = NumChunkDecimal & " "
End If

End If

Next LP
NumChunkDecimal = UCase(Trim(NumChunkDecimal)) ' :: UPPER AND TRIM ::

End Function

Private Function NumChunkCleanString(MyString As String) As String

Dim LP As Integer ' :: GENERIC LOOP VARIABLE ::

' :: RETURN CLEANSED STRING

MyString = Replace(MyString, " ", "") ' :: REMOVE SPACES
MyString = Replace(MyString, ",", "") ' :: REMOVE COMMAS
MyString = Replace(MyString, "'", "") ' :: REMOVE APOSTROPHES
MyString = StrReverse(Replace(StrReverse(MyString), ".", "|", , 1))   ' :: POINT TO PIPE FOR FIRST POINT
MyString = Replace(MyString, Chr(46), "")  ' :: NULL ALL ADDITIONAL POINTS ::
MyString = Replace(MyString, Chr(133), "")  ' :: NULL ALL ADDITIONAL POINTS ::

' :: REMOVE ANY ADDITIONAL DASHES ::
If Left(MyString, 1) = "-" And Len(MyString) > 1 Then MyString = "-" & Replace(Right(MyString, Len(MyString) - 1), "-", "")

':: REMOVE NON-NUMERIC CHARACTERS ::

For LP = 1 To Len(MyString)

If NumericDict(Asc(Mid(MyString, LP, 1))) = "Y" Then NumChunkCleanString = NumChunkCleanString & Mid(MyString, LP, 1)

Next LP

End Function

Private Function NumChunkWords(MyString As String) As String

Dim LP As Integer       ' :: GENERIC LOOP VARIABLE ::
Dim MyChunks As Integer ' :: CHUNKS OF 3 NUMBERS ::
Dim Outstring As String ' ::  RAW OUTPUT STRING ::
Dim PadString As String '  :: ZERO-PADDED NUMBER DIVISIBLE BY THREE ::
Dim MyPowerLUV As Double  ' :: POWER OF THREE-DIGIT PORTION ::
Dim MyPos As Integer  ' :: STRING POSITION CURSOR ::

' :: only use integer portion

MyChunks = NumChunkCount(MyString)  ' Count of three digit chunks

MyPos = 1

' :: CASE WHEN VALUE IS 0 ::
If MyChunks = 1 And PadString = "000" Then
NumChunkWords = "ZERO"
Exit Function
End If

For LP = 1 To MyChunks

MyPowerLUV = 10 ^ ((MyChunks - LP) * 3)  ' LOOKUP FOR POWER BRACKET ::

If MyPos = MyChunks Then
Outstring = Outstring & NumChunkWordBlock(Mid(PadString, (3 * LP) - 2, 3)) & " "

End If

If MyPos <> MyChunks Then
If Val(Mid(PadString, (3 * LP) - 2, 3)) > 0 Then
Outstring = Outstring & NumChunkWordBlock(Mid(PadString, (3 * LP) - 2, 3)) & " "
Outstring = Outstring & NumChunkName(MyPowerLUV) & " "
End If
End If

MyPos = MyPos + 1

Next LP

NumChunkWords = UCase(Trim(Outstring))

End Function

Private Function NumChunkWordBlock(MyString As String) As String

Dim LP As Index ' GENERIC LOOP
Dim NumHund, NumTen, NumUnit As Double '  Portions

NumChunkWordBlock = "" ' :: MAKE BLANK ::

NumHund = Val(Left(MyString, 1)) ' HUNDREDS
NumTen = Val(Mid(MyString, 2, 1)) ' TENS
NumUnit = Val(Right(MyString, 1)) ' UNITS

' :: DO HUNDREDS FIRST ::

If NumHund > 0 Then

NumChunkWordBlock = NumChunkName(NumHund) & " " & NumChunkName(100)

End If

':: ADD IN THE 'AND' WHERE TENS OR UNITS PORTION IS NOT ZERO

If NumTen + NumUnit > 0 And NumHund > 0 = True Then

NumChunkWordBlock = NumChunkWordBlock & " AND"

End If

':: CHECK FOR > 19

If (NumTen * 10) + NumUnit > 19 Then

NumChunkWordBlock = NumChunkWordBlock & " " & NumChunkName(NumTen * 10)

If NumUnit > 0 Then  ':: HAS UNITS?
NumChunkWordBlock = NumChunkWordBlock & " " & NumChunkName(NumUnit)
End If

End If

':: CHECK FOR <= 19

If (NumTen * 10) + NumUnit <= 19 And (NumTen * 10) + NumUnit > 0 Then

NumChunkWordBlock = NumChunkWordBlock & " " & NumChunkName((NumTen * 10) + NumUnit)

End If

End Function

Private Function NumChunkCount(MyString As String) As Integer

':: COUNT OF BLOCKS OF THREE DIGITS IN NUMBER ::

NumChunkCount = Len(Format(Val(MyString), "#,###")) - Len(Format(Val(MyString), "#")) + 1

End Function

Private Function NumChunkPadMe(ByVal MyString As String) As String

':: PAD STRING TO NEAREST MULTIPLE OF THREE ( ROUND UP!! )

End Function

Private Function NumericDict(ByVal MyInteger As Integer) As String

' :: RETURN ' Y ' OR  ' N ' IF VALID CHARACTER ::
Dim MyDict As Dictionary

' :: CHECK THE ASCII CODE PASSED AS MyInteger IS VALID NUMERIC CHARACTER ( OR VALID DELIMITER )

Set MyDict = New Dictionary
MyDict(48) = "Y" 'ZERO
MyDict(49) = "Y" 'ONE
MyDict(50) = "Y" 'TWO
MyDict(51) = "Y" 'THREE
MyDict(52) = "Y" 'FOUR
MyDict(53) = "Y" 'FIVE
MyDict(54) = "Y" 'SIX
MyDict(55) = "Y" 'SEVEN
MyDict(56) = "Y" 'EIGHT
MyDict(57) = "Y" 'NINE
MyDict(45) = "Y" 'DASH
MyDict(46) = "Y" 'POINT
MyDict(124) = "Y" 'PIPE

If MyDict.EXISTS(MyInteger) Then

NumericDict = MyDict(MyInteger)

Else

NumericDict = "N"

End If

End Function

Private Function NumChunkName(ByVal MyInteger As Double) As String

'
' ::  DICTIONARY TO CONVERT DOUBLE INPUT TO ENGLISH EQUIVALENT  ::
'

Dim MyDict As Dictionary

Set MyDict = New Dictionary

MyDict(0) = "zero"
MyDict(1) = "one"
MyDict(2) = "two"
MyDict(3) = "three"
MyDict(4) = "four"
MyDict(5) = "five"
MyDict(6) = "six"
MyDict(7) = "seven"
MyDict(8) = "eight"
MyDict(9) = "nine"
MyDict(10) = "ten"
MyDict(11) = "eleven"
MyDict(12) = "twelve"
MyDict(13) = "thirteen"
MyDict(14) = "fourteen"
MyDict(15) = "fifteen"
MyDict(16) = "sixteen"
MyDict(17) = "seventeen"
MyDict(18) = "eighteen"
MyDict(19) = "nineteen"
MyDict(20) = "twenty"
MyDict(30) = "thirty"
MyDict(40) = "forty"
MyDict(50) = "fifty"
MyDict(60) = "sixty"
MyDict(70) = "seventy"
MyDict(80) = "eighty"
MyDict(90) = "ninety"
MyDict(100) = "hundred"
MyDict(1000) = "thousand"
MyDict(1000000) = "million"
MyDict(1000000000) = "billion"
MyDict(1000000000000#) = "trillion"
MyDict(1E+18) = "quintillion"
MyDict(1E+21) = "Sextillion"
MyDict(1E+24) = "Septillion"
MyDict(1E+27) = "Octillion"
MyDict(1E+30) = "Nonillion"
MyDict(1E+33) = "Decillion"
MyDict(0.1) = "Tenth"
MyDict(0.01) = "Hundredth"
MyDict(0.001) = "Thousandth"
MyDict(0.0001) = "Ten-Thousandth"
MyDict(0.00001) = "Hundred-Thousandth"
MyDict(0.000001) = "Millionth"
MyDict(0.0000001) = "Ten-Millionth"
MyDict(0.00000001) = "Hundred-Millionth"
MyDict(0.000000001) = "Billionth"
MyDict(0.0000000001) = "Ten-Billionth"
MyDict(0.00000000001) = "Hundred-Billionth"
MyDict(0.000000000001) = "Trillionth"
MyDict(0.0000000000001) = "Ten-Trillionth"
MyDict(0.00000000000001) = "Hundreed-Trillionth"
MyDict(1E-18) = "Quitillionth"
MyDict(1E-19) = "Ten-Quitillionth"
MyDict(1E-20) = "Hundred-Quitillionth"
MyDict(1E-21) = "Sextillionth"
MyDict(1E-22) = "Ten-Sextillionth"
MyDict(1E-23) = "Hundred-Sextillionth"
MyDict(1E-24) = "Septillionth"

If MyDict.EXISTS(MyInteger) Then

NumChunkName = MyDict(MyInteger)

Else

NumChunkName = ""

End If

End Function

Function ZeroPad(MyInput As Variant, ZeroCount As Integer) As String

ZeroPad = Right(WorksheetFunction.Rept("0", ZeroCount) & CStr(MyInput), ZeroCount)

End Function

```
```