|
Converting Numeric values to Text
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,FA LSE)&" "&VLOOKUP(LEN(E9),C2:D2,2,FALSE)&" "&IF(ISERROR(VLOOKUP(VALUE(LEFT(MID(E9,2,3),1)),A1 :B9,2,FALSE)&" "&VLOOKUP(LEN(MID(E9,2,3)),C1:D2,2,FALSE)),"",VLOO KUP(VALUE(LEFT(MID(E9,2,3),1)),A1:B9,2,FALSE)&" "&VLOOKUP(LEN(MID(E9,2,3)),C1:D2,2,FALSE))&" "&IF(ISERROR(IF(AND(VALUE(MID(E9,4,1))<0,VALUE(MI D(E9,3,1))<1),VLOOKUP(VALUE(MID(E9,3,1)&0),A20:B2 7,2,FALSE),"")),"",IF(AND(VALUE(MID(E9,4,1))<0,VA LUE(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(VALU E(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,FA LSE)&" "&VLOOKUP(LEN(E9),C1:D1,2,FALSE)&" "&IF(ISERROR(IF(VALUE(MID(E9,3,1))<0,VLOOKUP(VALU E(MID(E9,2,1)&0),A20:B27,2,FALSE))),"",IF(VALUE(MI D(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(VALU E(MID(E9,3,1)),A1:B9,2,FALSE),"")),"",IF(VALUE(MID (E9,2,1))<1,VLOOKUP(VALUE(MID(E9,3,1)),A1:B9,2,FA LSE),""))&" "&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,V LOOKUP(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,FALS E),""))&" "&IF(ISERROR(IF(VALUE(LEFT(E9,1))<0,VLOOKUP(E9,A1 0:B27,2,FALSE))),"",IF(VALUE(LEFT(E9,1))<0,VLOOKU P(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 |
Converting Numeric values to Text
Why re-invent the wheel?
Look at SPELLNUMBER ... http://support.microsoft.com/default...b;en-us;213360 "shail" wrote: 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,FA LSE)&" "&VLOOKUP(LEN(E9),C2:D2,2,FALSE)&" "&IF(ISERROR(VLOOKUP(VALUE(LEFT(MID(E9,2,3),1)),A1 :B9,2,FALSE)&" "&VLOOKUP(LEN(MID(E9,2,3)),C1:D2,2,FALSE)),"",VLOO KUP(VALUE(LEFT(MID(E9,2,3),1)),A1:B9,2,FALSE)&" "&VLOOKUP(LEN(MID(E9,2,3)),C1:D2,2,FALSE))&" "&IF(ISERROR(IF(AND(VALUE(MID(E9,4,1))<0,VALUE(MI D(E9,3,1))<1),VLOOKUP(VALUE(MID(E9,3,1)&0),A20:B2 7,2,FALSE),"")),"",IF(AND(VALUE(MID(E9,4,1))<0,VA LUE(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(VALU E(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,FA LSE)&" "&VLOOKUP(LEN(E9),C1:D1,2,FALSE)&" "&IF(ISERROR(IF(VALUE(MID(E9,3,1))<0,VLOOKUP(VALU E(MID(E9,2,1)&0),A20:B27,2,FALSE))),"",IF(VALUE(MI D(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(VALU E(MID(E9,3,1)),A1:B9,2,FALSE),"")),"",IF(VALUE(MID (E9,2,1))<1,VLOOKUP(VALUE(MID(E9,3,1)),A1:B9,2,FA LSE),""))&" "&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,V LOOKUP(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,FALS E),""))&" "&IF(ISERROR(IF(VALUE(LEFT(E9,1))<0,VLOOKUP(E9,A1 0:B27,2,FALSE))),"",IF(VALUE(LEFT(E9,1))<0,VLOOKU P(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 |
Converting Numeric values to Text
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 Toppers wrote: Why re-invent the wheel? Look at SPELLNUMBER ... http://support.microsoft.com/default...b;en-us;213360 |
| All times are GMT +1. The time now is 01:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com