# Solved how to create a formulas in excel.

September 9, 2016 at 00:12:55
Specs: Windows xp pro
 Sir,how to create our own formula in excel to convert digits into words.example:- 3 yrs 4 months to three years four months.)message edited by GulamMurtuza

See More: how to create a formulas in excel.

September 9, 2016 at 20:46:49
 This is similar to DerbyDads, but I used the INDEX() functionand a separate sheet to hold the Number spellings.To use it:First create a separate worksheet to hold the number spellings, I used the sheet name "NmWrd" and it is simply a single column like: A 1) Zero 2) One 3) Two 4) Three 5) Four 6) Five 7) Six 8) Seven 9) Eight Now on your primary worksheet, in cell A1enter your string: "3 Years 4 Months"Next, in cell B1 enter the formula:=INDEX(NmWrd!A:A,INT(MID(A1,1,FIND("y",A1)-2))+1)&" Year"&IF(INT(MID(A1,1,FIND("y",A1)-2))=1," ","s ")&INDEX(NmWrd!A:A,INT(MID(A1,FIND("s",A1)+2,(FIND("m",A1)-1)-(FIND("s",A1)+2))+1))&" Month"&IF(INT(MID(A1,FIND("s",A1)+2,(FIND("m",A1)-1)-(FIND("s",A1)+2)))=1,"","s")You can expand your list of numbers on the NmWrd sheet down as far as you like,my test sheet went down to One Hundred. MIKEhttp://www.skeptic.com/

#1
September 9, 2016 at 05:47:05
 I'm no "Sir," but you can find the code you need by searching Excel help for convert numbers to words. It uses the SpellNumber function code in VBA.

Report •

#2
September 9, 2016 at 06:50:52
 Assuming that beachyhbt is referring to the SpellNumber function found here...https://support.microsoft.com/en-us......that code will require extensive modification since it was written to convert monetary values given in numbers into text versions of that monetary value, e.g. 10.05 ---> Ten Dollars and Five Cents The following formula seems to accomplish requested task. It's not perfect, in that 1 yr will become one years (same for 1 months), but that could be dealt with if need be.Follow these steps. You can use any columns you want, mine are just examples.1 - Format Column C as Text2 - Create the following table, making it as long as you need to cover the numbers of years that you will be dealing with. I stopped at 13 because you need at least 12 to cover all months.Remember that Column C must be formatted as Text C D 1 1 one 2 2 two 3 3 three 4 4 four 5 5 five 6 6 six 7 7 seven 8 8 eight 9 9 nine 10 10 ten 11 11 eleven 12 12 twelve 13 13 thirteen With 3 yrs 4 months in A1, enter this formula in B1:=VLOOKUP(MID(A1,1,FIND("y",A1)-2),\$C\$1:\$D\$13,2,0)&" years "&VLOOKUP(MID(A1,FIND("s",A1)+2,(FIND("m",A1)-1)-(FIND("s",A1)+2)),\$C\$1:\$D\$13,2,0)&" months"It should return three years four months as requested.

Report •

#3
September 9, 2016 at 08:37:08
 I played around a little more and came up with a formula that handles the 1 yr and 1 month situations better:=VLOOKUP(MID(A1,1,FIND("y",A1)-2),\$C\$1:\$D\$13,2,0)&IF(MID(A1,1,FIND("y",A1)-2)="1"," year "," years ")&VLOOKUP(MID(A1,FIND("s",A1)+2,(FIND("m",A1)-1)-(FIND("s",A1)+2)),\$C\$1:\$D\$13,2,0)&IF(MID(A1,FIND("s",A1)+2,(FIND("m",A1)-1)-(FIND("s",A1)+2))="1"," month"," months")

Report •

Related Solutions

#4
September 9, 2016 at 20:46:49