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.

Report •

✔ Best Answer
September 9, 2016 at 20:46:49
This is similar to DerbyDads, but I used the INDEX() function
and 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 A1
enter 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.

MIKE

http://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 Text
2 - 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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
September 9, 2016 at 20:46:49
✔ Best Answer
This is similar to DerbyDads, but I used the INDEX() function
and 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 A1
enter 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.

MIKE

http://www.skeptic.com/


Report •

#5
September 10, 2016 at 04:43:54
Mike: I realized later on that I didn't deal with "zero" so I'm glad you took care of that.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question