Excel convertion help

Microsoft Microsoft office excel 2007 ac...
March 27, 2010 at 07:23:12
Specs: Windows Vista
I need to convert amount in word like:
3450.34 as " Rupees three thousand and fifty and cents thirty four into mauritian rupees and proper case


See More: Excel convertion help

Report •

#1
March 27, 2010 at 08:06:08
Excel questions are best asked in the Office Software forum.

That being said, try here:

http://www.fixya.com/support/t10412...

Solution #2 are custom formats that you can use.

MIKE

http://www.skeptic.com/


Report •

#2
March 27, 2010 at 08:08:09

Report •

#3
March 27, 2010 at 09:34:44
hi suresh

thanks but wish to convert same but in mauritian rupees... that is instead of using paisa used cents and if possible in proper case


Report •

Related Solutions

#4
March 27, 2010 at 09:49:33
thanks but looking for convertion into mauritian rupees..... suresh could i get the coding of the formula

Report •

#5
March 27, 2010 at 15:34:57
Sorry, I do not know enough about the difference.

If you ask your question, in specific detail, over at the Office Software forum someone may be able to help.
They are very good with VBA and there have been other similar questions resolved.

MIKE

http://www.skeptic.com/


Report •

#6
March 28, 2010 at 07:22:06
Hi,

Without any understanding of Mauritian Rupees, I can still give you a possible solution that you should be able to adapt to your specific needs.

This is a set of formulas that breakdown the number into thousands, hundreds, tens/units and the decimal part.
Then a lookup tables convert the numbers into words
and finally a formula concatenates (joins) the 'numbers as words' with the text for the currency.

In cell B3 enter the number e.g., 3450.34
In cells F3 to G12 create a table of numbers and words as follows:

	F	G
3	0	
4	10	Ten
5	20	Twenty
6	30	Thirty
7	40	Fourty
8	50	Fifty
9	60	Sixty
10	70	Seventy
11	80	Eighty
12	90	Ninety
Cell G3 contains =""
In cells H3 to I23 create a second table as follows:
	H	I
3	0	
4	1	One
5	2	Two
6	3	Three
7	4	Four
8	5	Five
9	6	Six
10	7	Seven
11	8	Eight
12	9	Nine
13	10	Ten
14	11	Eleven
15	12	Twelve
16	13	Thirteen
17	14	Fourteen
18	15	Fifteen
19	16	Sixteen
20	17	Seventeen
21	18	Eighteen
22	19	Nineteen
23	20	Twenty
Cell I3 contains =""

Enter the following formulas in the specified cells

C3	=INT(B3/1000)
C4	=INT((B3-(INT(B3/1000)*1000))/100)
C5	=INT(B3-(INT(B3/1000)*1000)-(INT((B3-(INT(B3/1000)*1000))/100))*100)
C6	=(B3-INT(B3))*100
These will show the numbers for thousands, hundreds etc.

Enter the following formulas in the specified cells

D3	=IF(C3<20,VLOOKUP(C3,H3:I23,2,FALSE),VLOOKUP(INT(C3/10)*10,F3:G12,2,FALSE) 
& IF(C3=INT(C3), ""," " &VLOOKUP(C3-INT(C3/10)*10,H3:I23,2,FALSE)))
D4	=VLOOKUP(C4,H3:I12,2,FALSE)
D5	=IF(C5<20,VLOOKUP(C5,H3:I23,2,FALSE),VLOOKUP(INT(C5/10)*10,F3:G12,2,FALSE) & IF(C5=INT(C5/10),""," " &VLOOKUP(C5-INT(C5/10)*10,H3:I23,2,FALSE)))
Note that some formulas have been split over two rows for ease of viewing - enter them as single formulas.

In cell E3, enter this formula which creates the final text:

="Stirling " & D3 & " thousand, " & D4 & " hundred" & IF(C5=0,""," and ") & 
D5 & " Pounds" & IF(C6=0,""," and " & ROUND(C6,2) & " pence")
The formula has been split onto two lines for ease of viewing.
Replace/remove/add words suitable for Mauritian Rupees.

Hope this helps you get the solution you are looking for.

(The formulas could be embedded into fewer cells - possibly just one cell, but for the purposes of testing and debugging, I found that using seperate cells was easier).

If you need to exceed 90000 then the first lookup table will need to be extended.

If you are going to use millions then another section will need to be added, with a series of changes to the formulas.

Here are a couple of conversions:
20902.12
Stirling Twenty thousand, Nine hundred and Two Pounds and 12 pence
3100.12
Stirling Three thousand, One hundred Pounds and 10 pence

Regards


Report •

Ask Question