Converting Numeric values to Text

[shail]

Hi,

I have made a nested fomula which comverts numeric value to words (for
example - 101 to One Hundred and One). For this I had numbers from 1 to
20 in column A1 till A20 and 30, 40, 50... till 90 from A21 through
A27. And the corresponding value in words to the next column i.e., B1
to B27.
And at range C1: D2 as
3 | Hundred
4 | Thousand

I have four nested formula named type1, type2, type3 and type4

type1 as :

=IF(LEN(E9)=4,VLOOKUP(VALUE(LEFT(E9,1)),A1:B9,2,FALSE)&"
"&VLOOKUP(LEN(E9),C22,2,FALSE)&"
"&IF(ISERROR(VLOOKUP(VALUE(LEFT(MID(E9,2,3),1)),A1:B9,2,FALSE)&"
"&VLOOKUP(LEN(MID(E9,2,3)),C12,2,FALSE)),"",VLOOKUP(VALUE(LEFT(MID(E9,2,3),1)),A1:B9,2,FALSE)&"
"&VLOOKUP(LEN(MID(E9,2,3)),C12,2,FALSE))&"
"&IF(ISERROR(IF(AND(VALUE(MID(E9,4,1))<>0,VALUE(MID(E9,3,1))<>1),VLOOKUP(VALUE(MID(E9,3,1)&0),A20:B27,2,FALSE),"")),"",IF(AND(VALUE(MID(E9,4,1))<>0,VALUE(MID(E9,3,1))<>1),VLOOKUP(VALUE(MID(E9,3,1)&0),A20:B27,2,FALSE),""))&"
"&IF(ISERROR(IF(VALUE(MID(E9,3,1))<>1,VLOOKUP(VALUE(MID(E9,4,1)),A1:B9,2,FALSE))),"",IF(VALUE(MID(E9,3,1))<>1,"and
"&VLOOKUP(VALUE(MID(E9,4,1)),A1:B9,2,FALSE),""))&"
"&IF(ISERROR(VLOOKUP(VALUE(MID(E9,3,2)),A10:B27,2,FALSE)),"","and
"&VLOOKUP(VALUE(MID(E9,3,2)),A10:B27,2,FALSE)),"")

type2 as:

=IF(LEN(E9)=3,VLOOKUP(VALUE(LEFT(E9,1)),A1:B9,2,FALSE)&"
"&VLOOKUP(LEN(E9),C11,2,FALSE)&"
"&IF(ISERROR(IF(VALUE(MID(E9,3,1))<>0,VLOOKUP(VALUE(MID(E9,2,1)&0),A20:B27,2,FALSE))),"",IF(VALUE(MID(E9,3,1))<>0,"and
"&VLOOKUP(VALUE(MID(E9,2,1)&0),A20:B27,2,FALSE),""))&"
"&IF(ISERROR(IF(VALUE(MID(E9,2,1))<>1,VLOOKUP(VALUE(MID(E9,3,1)),A1:B9,2,FALSE),"")),"",IF(VALUE(MID(E9,2,1))<>1,VLOOKUP(VALUE(MID(E9,3,1)),A1:B9,2,FALSE),""))&"
"&IF(ISERROR(VLOOKUP(VALUE(MID(E9,2,2)),A10:B27,2,FALSE)),"","and
"&VLOOKUP(VALUE(MID(E9,2,2)),A10:B27,2,FALSE)),"")

type3 as:

=IF(LEN(E9)=2,IF(ISERROR(IF(VALUE(RIGHT(E9,1))>0,VLOOKUP(VALUE(LEFT(E9,1)&0),A20:B27,2,FALSE),"")),"",IF(VALUE(RIGHT(E9,1))>0,VLOOKUP(VALUE(LEFT(E9,1)&0),A20:B27,2,FALSE),""))&"
"&IF(ISERROR(IF(VALUE(LEFT(E9,1))<>1,VLOOKUP(VALUE(RIGHT(E9,1)),A1:B9,2,FALSE),"")),"",IF(VALUE(LEFT(E9,1))<>1,VLOOKUP(VALUE(RIGHT(E9,1)),A1:B9,2,FALSE),""))&"
"&IF(ISERROR(IF(VALUE(LEFT(E9,1))<>0,VLOOKUP(E9,A10:B27,2,FALSE))),"",IF(VALUE(LEFT(E9,1))<>0,VLOOKUP(E9,A10:B27,2,FALSE))),"")

and type4 as:

=IF(LEN(E9)=1,VLOOKUP(E9,A1:B9,2,FALSE),"")

and these 4 named formulas as:

=type1&type2&type3&type4

Everything is working fine. Just sometimes an extra space appears
between the words. I know why it is appearing, but I couldn't be able
to fix it. Also my formula doesn't work with decimals. It treats them
as another character and give the wrong output. Lastly, my formula
works for 4 digit numbers only.

Can you remove the extra spaces between words and make it for decimals
too.

Thanks,

Shail

 

[Guest]

Why re-invent the wheel?

Look at SPELLNUMBER ...

http://support.microsoft.com/default.aspx?scid=kb;en-us;213360

 

[shail]

Hi Topppers,

I wanted to do it without the VBA codes. I have been watching your (and
Bob Phillips, David Billigmeier, Leo Heuser's ) posts for long and I
need your lelp in it. Also please tell me if there is a way simpler
than what I have developed.

Thanks Toppers,

Shail