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

✔ 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

NmWrdsheet down as far as you like,

my test sheet went down to One Hundred.MIKE

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.

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 year

s(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 thirteenWith

3 yrs 4 monthsin 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 monthsas requested.

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

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.

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

NmWrdsheet down as far as you like,

my test sheet went down to One Hundred.MIKE

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.

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History