Numbers to words

[geordie_ben]

I have an issue I'd like to run by the masses

I need to convert numbers to words.

So 100.09 becomes One Hundred Pounds and Nine Pence

I know there are multiple macros and plugins to allow this

My issue comes than I can't use any of these, as the Excel file will be passed through Conga, which ignores any macros or plugins.

I've thought of using a multi line IF statement, but I have a feeling I'd hit a limit as I'd need every penny option from 1p to £100,000

Another option would be a VLOOKUP, but if I add this to the Excel document, when Conga grabs the page it will also include all the iterations for the Vlookup as part of the PDF

Can anyone think of any other options?

 

[geordie_ben]

Mark, that is amazing!!

One issue, it fails on figures of hundred thousands, 100000 shows as One Hundred And Thousand Pounds, instead of One Hundred Thousand Pounds

 

[MARK858]

I'm afraid it will have to wait until the weekend as I am just off to work. I'll see if I can look at it then.

 

[oldbrewer]

my drop box link

https://www.dropbox.com/s/r33rvjnafmriguv/numstowords.xls?dl=0

 

[MARK858]

@geordie_ben, I haven't tested the below extensively so it might have issues...

Excel Workbook
	A	B
1	265232.00	Two Hundred Sixty-Five Thousand Two Hundred And Thirty-Two Pounds
2	64532.26	Sixty-Four Thousand Five Hundred And Thirty-Two Pounds And Twenty-Five Pence
3	1.36	One Pound And Thirty-Six Pence
4	2569.87	Two Thousand Five Hundred And Sixty-Nine Pounds And Eighty-Seven Pence
5	148569756.64	One Hundred And Forty-Eight Million Five Hundred Sixty-Nine Thousand Seven Hundred And Fifty-Six Pounds And Sixty-Four Pence
6	999999999999.99	Nine Hundred And Ninety-Nine Billion Nine Hundred And Ninety-Nine Million Nine Hundred Ninety-Nine Thousand Nine Hundred And Ninety-Nine Pounds And Ninety-Nine Pence
7	100000.00	One Hundred Thousand Pounds
8	465125.00	Four Hundred Sixty-Five Thousand One Hundred And Twenty-Five Pounds
9	12526.01	Twelve Thousand Five Hundred And Twenty-Six Pounds And One Pence
10	0.26	Twenty-Six Pence
11	0.49	Forty-Nine Pence
12	0.52	Fifty-Two Pence
13	0.05	Five Pence
Sheet1