What formula would be required to show the total amount, say £175.50, in words, one hundred and seventy five pounds fifty pence? I’m using Excel 2010 but it converts down to lower packages.

What formula would be required to show the total amount, say £175.50, in words, one hundred and seventy five pounds fifty pence? I’m using Excel 2010 but it converts down to lower packages.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners. How Google uses data when you use our partners' sites or apps

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Hi

You will need to create a custom Microsoft Excel function that will convert numeric currency values into words for you. Microsoft have published the VBA for this and I have tweaked it to so that it is compatible with UK currency.

The code below details what you need to enter into a new module in the VBA editor. As I said this is Microsoft code that I have tweaked to be UK compatible.

`Option Explicit`

`'****************`

`' Main Function *`

`'****************`

`Function SpellNumber(ByVal MyNumber)`

`Dim Pounds, Pence, Temp`

`Dim DecimalPlace, Count`

`'Dim GetTens, GetHundreds`

`ReDim Place(9) As String`

`Place(2) = " Thousand "`

`Place(3) = " Million "`

`Place(4) = " Billion "`

`Place(5) = " Trillion "`

`' String representation of amount.`

`MyNumber = Trim(Str(MyNumber))`

`' Position of decimal place 0 if none.`

`DecimalPlace = InStr(MyNumber, ".")`

`' Convert cents and set MyNumber to dollar amount.`

`If DecimalPlace > 0 Then`

`Pence = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))`

`MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))`

`End If`

`Count = 1`

`Do While MyNumber <> ""`

`Temp = GetHundreds(Right(MyNumber, 3))`

`If Temp <> "" Then Pounds = Temp & Place(Count) & Pounds`

`If Len(MyNumber) > 3 Then`

`MyNumber = Left(MyNumber, Len(MyNumber) - 3)`

`Else`

`MyNumber = ""`

`End If`

`Count = Count + 1`

`Loop`

`Select Case Pounds`

`Case ""`

`Pounds = "No Pounds"`

`Case "One"`

`Pounds = "One Pound"`

`Case Else`

`Pounds = Pounds & " Pounds"`

`End Select`

`Select Case Pence`

`Case ""`

`Pence = " and No Pence"`

`Case "One"`

`Pence = " and One Pence"`

`Case Else`

`Pence = " and " & Pence & " Pence"`

`End Select`

`SpellNumber = Pounds & Pence`

`End Function`

`'*******************************************`

`' Converts a number from 100-999 into text *`

`'*******************************************`

`Function GetHundreds(ByVal MyNumber)`

`Dim Result As String`

`If Val(MyNumber) = 0 Then Exit Function`

`MyNumber = Right("000" & MyNumber, 3)`

`' Convert the hundreds place.`

`If Mid(MyNumber, 1, 1) <> "0" Then`

`Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "`

`End If`

`' Convert the tens and ones place.`

`If Mid(MyNumber, 2, 1) <> "0" Then`

`Result = Result & GetTens(Mid(MyNumber, 2))`

`Else`

`Result = Result & GetDigit(Mid(MyNumber, 3))`

`End If`

`GetHundreds = Result`

`End Function`

`'*********************************************`

`' Converts a number from 10 to 99 into text. *`

`'*********************************************`

`Function GetTens(TensText)`

`Dim Result As String`

`Result = "" ' Null out the temporary function value.`

`If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...`

`Select Case Val(TensText)`

`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 ' If value between 20-99...`

`Select Case Val(Left(TensText, 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 & GetDigit(Right(TensText, 1)) ' Retrieve ones place.`

`End If`

`GetTens = Result`

`End Function`

`'*******************************************`

`' Converts a number from 1 to 9 into text. *`

`'*******************************************`

`Function GetDigit(Digit)`

`Select Case Val(Digit)`

`Case 1: GetDigit = "One"`

`Case 2: GetDigit = "Two"`

`Case 3: GetDigit = "Three"`

`Case 4: GetDigit = "Four"`

`Case 5: GetDigit = "Five"`

`Case 6: GetDigit = "Six"`

`Case 7: GetDigit = "Seven"`

`Case 8: GetDigit = "Eight"`

`Case 9: GetDigit = "Nine"`

`Case Else: GetDigit = ""`

`End Select`

`End Function`

Once you have the code above in module it is fairly simple to use. Either type in a cell

`=spellnumbner(55.30)`

will produce Fifty Five Pounds and Thirty Pence. Just change the value inside the brackets to that which you want spelt. Or,if there is a currency value already entered in a cell, eg cell A1, then type`=spellnumber(A1)`

. Again swap the cell reference inside the brackets to that of the cell you wish to reference.I hope this helps! You can also download the Excel file containing the VBA code to convert numeric values to words.

Thanks for writing and please do recommend Excel Geek.

Unfortunately, the code doesn’t catch that unique (non-US) habit we brits have of leaving the ‘and’ out of a hundreds number if the tens and units are zero. For example, £200.67 is ‘two hundred pounds and sixty seven pence’, whereas £234.67 is ‘two hundred AND thirty four pounds and sixty seven pence. I’ve tried to modify this code to catch this exception but so far all it has done is drive me nuts!!

Hey, thanks for writing. Try this modification… it should put the ‘and’ in for you. 🙂

Navigate down the code to where these five lines are. It starts

.`' Convert the hundreds place.`

`' Convert the hundreds place.`

`If Mid(MyNumber, 1, 1) <> "0" Then`

`Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "`

`Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred and "`

`End If`

Then replace those five lines above with the code below. 🙂

`' Convert the hundreds place.`

`If Mid(MyNumber, 1, 1) <> "0" Then`

`Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred and "`

`End If`

`If Mid(MyNumber, 2, 2) = "00" Then`

`'Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "`

`Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "`

`End If`

I hope this helps!

Hey Guys,

Sorry to latch on to your thread but hoping you can help as I’m stuck.

Thanks for the above, i’ve used your VBA above for the correct SpellNumber in UK currency and also edited it to correct the English preference to include “and” after the hundred number.

Another habit I’ve tried to edit by myself but can’t get it to work is to include comma’s after the trillion, billion, million and thousand.

Using the above VBA the number “1234567891.23” would appear as:

“One Billion Two Hundred and Thirty Four Million Five Hundred and Sixty Seven Thousand Eight Hundred and Ninety One Pounds and Twenty Three Pence”

Does anyone know how to edit the VBA or can issue a revised VBA so that the above figure and any edit would

“One Billion, Two Hundred and Thirty Four Million, Five Hundred and Sixty Seven Thousand, Eight Hundred and Ninety One Pounds and Twenty Three Pence”

Any help would be very much appreciated!

Regards,

Peter

What if I want to add hyphen in the range between (21 and 99) ?

EX:

1872

One Thousand Eight Hundred Seventy-Two

1920

One Thousand Nine Hundred Twenty

Hi can any one me to write 1020 as “one thousand and twenty” please.