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?

 

[oldbrewer]

what is the maximum amount eg 500, 5000, 50000 ?

 

[geordie_ben]

I think it's going to be 100,000

 

[oldbrewer]

		899123456
		899123456.78
												mytable
	length	9
	pence	0.785	seventy	eight pence
	millions	899	eight hundred and	ninety	nine million
	thousands	123	one hundred and	twenty	three thousand							1	one
	hundreds	4	four hundred and									2	two
	tens	5	fifty									3	three
	units	6	six pounds and									4	four
												5	five
												6	six
												7	seven
												8	eight
												9	nine
			a bit clunky but it works								10	ten
												11	eleven
			c3 is the original amount and c1 is the integer amount						12	twelve
												13	thirteen
			length is the length of the integer							14	fourteen
												15	fifteen
			mytable is the lookup table								16	sixteen
												17	seventeen
												18	eighteen
			GOOD LUCK !!!									19	nineteen
												20	twenty
												30	thirty
												40	forty
												50	fifty
												60	sixty
												70	seventy
												80	eighty
												90	ninety
												100	hundred
												1000	thousand
												1000000	million
	eight hundred and ninety nine million one hundred and twenty three thousand four hundred and fifty six pounds and seventy eight pence

<colgroup><col span="2"><col><col><col><col><col span="10"></colgroup><tbody>
</tbody>

 

[geordie_ben]

Hi, thank looks great

I'm guessing there's a lot of formulas behind it.

 

[oldbrewer]

this site does not allow that - if you can get dispensation from a mod that is fine but I doubt you will

sorry

 

[hiker95]

geordie_ben,

It is always easier to help and test possible solutions if we could work with your actual file.

Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com.

Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

If the workbook contains confidential information, you could replace it with generic data.

 

[Fluff]

@geordie_ben
I have edited post#5, as what you were asking is against board policy. Please see rule#4.

 

[geordie_ben]

Thanks for the heads up about the rules.

Are you able to paste the formula version of the above into a reply on here?

I'm not even sure if that is possible - here's hoping

 

[MARK858]

For values above 1 pound

Excel Workbook
	A	B
1	15426.26	Fifteen Thousand Four Hundred And Twenty-Six Pounds And Twenty-Six Pence
2	64532.256	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 And 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 And Ninety-Nine Thousand Nine Hundred And Ninety-Nine Pounds And Ninety-Nine Pence
7
Sheet1