Converting Numbers to Words in MS Excel

-

Numbers to Words
Converting numbers to words is not an easy job in MS Excel even though no such function is available to do this task. I have tried lots of functions to achieve the conversion of the numeral to words but all went in vain. (There is a small video for your reference which shows the conversion of numerals to words at the end of this blog.)

Then I developed a formula to do this task easily. The formula is a bit complex and lengthy but the result is awesome. You can convert the numerals to words with a click only. 

Creating a Table of List

For converting numbers to words you need to create a list somewhere in your excel sheet in which you want to use it. It is a one-time job, thereafter you can copy and paste the formula into different sheets and complete your task. Just you have to change the references mentioned in the formula according to your requirement.

You have to create a list of numerals from 1 to 99. It will be a two-column table in which the first column has numerals and the second column has the text form of the numeral, just like given below:

1 One

2 Two

3 Three

4 Four

5 Five.....  so on up to 99 

After creating this table, you have to use the following command to convert a number to text from one to Ninety Nine only.

Formula to Calculate Numbers to Words (From 1 to 99)

=IF(OR(LEN(C1)=1,LEN(C1)=2),VLOOKUP(C1,J1:K99,2))

In this formula; 

C1= Cell reference  (You can change the cell reference according to your need)

J1: K99 (you can change the table reference according to your need) is a reference for a list of numerals 1 to 99 in a table as shown above. (You have to create this type of table somewhere in the excel sheet to hide it from easy sight.)

There are four formulas used in the above formula to convert numerals to words. 

The first is,  the If () function for nesting if certain conditions are met or not.

OR() function gives two choices of conditions, either met to run the formula. 

LEN() this function works as a trick to count the length of the numeral.

VLOOKUP() is used to extract a value from a table based on certain conditions.

Formula to Calculate Numbers to Words (From 100 to 999)

The below formula converts numbers to words from a hundred to Nine hundred ninety-nine only.

=IF(AND(LEN(C1)=3,MID(C1,2,3)<>"00"),VLOOKUP(VALUE(MID(C1,1,1)),J1:K99,2) &""&"Hundred"&""&VLOOKUP(VALUE(MID(C1,2,2)),J1:K99,2),IF(AND(LEN(C1)=3,MID(C1,2,3)="00"), VLOOKUP(VALUE(MID(C1,1,1)),J1:K99,2) & " " & "Hundred"))

In this formula, we have used AND(), MID(), Vlookup(), and LEN() functions to convert numerals to words.

Formula to Calculate Numbers to Words (From 1000 to 9999)

The below formula generates numerals to words from One Thousand to Nine Thousand nine hundred ninety-nine only.

=IF(AND(LEN(C1)=4,MID(C1,3,4)<>"000"),VLOOKUP(VALUE(MID(C1,1,1)),J1:K99 ,2)&""&"Thousand"&""&IF(MID(C1,2,1<>"0",VLOOKUP(VALUE(MID(C1,2,1)),J1:K99,2)&""&"Hundred"&"",IF(MID(C1,2,1)<>"0",VLOOKUP(VALUE(MID(C1,3,2)),J1:K99 ,2) ," ")))&IF(RIGHT(C1,2) <> "00",VLOOKUP(VALUE(MID(C1,3,2)),J1:K99,2)," ")

There are additional And(), MID(), Value() AND Right () function used to converts the words from numbers. 

In this way, we can convert further numerals to words. If the formula is written perfectly you can super easily convert any numeral to a word up to a number as desired by you. 

While this formula is quite lengthy and a little bit confusing but it works perfectly. I have tried many times to convert numerals to text and the result was awesome. 

This formula works in all versions of MS Excel and you can super easily convert the numbers to word.

Formula to Calculate Numbers to Words (From 1 to 999999)

This formula is a combination of all the above formulas. This formula can convert the numbers to text very easily as shown in the video for your reference. 

=TRIM(IFERROR(IF(OR(LEN(C1)=1,LEN(C1)=2),IF(OR(LEN(C1)=1,LEN(C1)=2),VLOOKUP(C1,J1:K99,2)),IF(LEN(C1)=3,IF(AND(LEN(C1)=3,MID(C1,2,3)>"00"),VLOOKUP(VALUE(MID(C1,1,1)),J1:K99,2)&""&"Hundred"&""&VLOOKUP(VALUE(MID(C1,2,2)),J1:K99,2),IF(AND(LEN(C1)=3,MID(C1,2,3)="00"),VLOOKUP(VALUE(MID(C1,1,1)),J1:K99,2)&""&"Hundred")),IF(LEN(C1)=4,IF(AND(LEN(C1)=4,MID(C1,3,4)>"000"),VLOOKUP(VALUE(MID(C1,1,1)),J1:K99,2)&""&"Thousand"&""&IF(MID(C1,2,1)>"0",VLOOKUP(VALUE(MID(C1,2,1)),J1:K99,2)&""&"Hundred"&"",IF(MID(C1,2,1) <>"0",VLOOKUP(VALUE(MID(C1,3,2)),J1:K99,2),"")))&IF(RIGHT(C1,2)<>"00",VLOOKUP(VALUE(MID(C1,3,2)),J1:K99,2),""),IF(LEN(C1)=5,IF(AND(LEN(C1)=5,MID(C1,4,5)>"000"),VLOOKUP(VALUE(MID(C1,1,2)),J1:K99,2)&""&"Thousand"&" "&IF(MID(C1,3,1)<>"0",VLOOKUP(VALUE(MID(C1,3,1)),J1:K19,2)&" "&"Hundred"&" ",IF(MID(C1,3,1)>"0",VLOOKUP(VALUE(MID(C1,4,2)),J1:K99,2),"")))&IF(RIGHT(C1,2)<>"00",VLOOKUP(VALUE(MID(C1,4,2)),J1:K99,2),""),IF(AND(LEN(C1)=6,MID(C1,5,6)<>"0000"),VLOOKUP(VALUE(MID(C1,1,1)),J1:K99,2)&" "&"Lac"&" "&IF(MID(C1,2,2)<>"00",VLOOKUP(VALUE(MID(C1,2,2)),J1:K99,2)&""&"Thousand","") &" "&IF(MID(C1,4,1)>"0",VLOOKUP(VALUE(MID(C1,4,1)),J1:K99,2)&""&"Hundred "&"",IF(MID(C1,2,1)="00",VLOOKUP(VALUE(MID(C1,5,2)),J1:K99,2),"")))&" "&IF(RIGHT(C1,2)<>"00",VLOOKUP(VALUE(MID(C1,5,2)),J1:K99,2),""))))),""))


Testing Video for your reference :
In this video, the formula is in combined form to convert numbers to words. 

Comments

Post a Comment

Popular posts from this blog

WHAT SIDE OF THE PAPER DOES THE PRINTER PRINT ON ?

-
Image
It is a common practice by all of us to forget the orientation of the paper in case of printing over a Letter Head or pre-printed paper.  Printer Paper Orientation  This way in order to save the paper, we furthermore generate the waste. Besides this, many times we print the document on assumption and again waste the paper, ink, time & energy. Surely, we can utilize the one-sided paper for the draft printing of documents.   But lack of knowledge about printers leads us to repeat this mistake again and again and unintentionally generate lots of waste. But we can save the papers, ink, and our precious time too if we keep one thing in mind and then print the documents i.e  understand the following icons :  How to place the printed paper on the Printer? The icon embossed over the paper tray tells us about the orientation of the paper quite clearly.  But, we simply ignore it and print it without knowing its worth. Generally, in haste or stress, we often forge...
Read more

Metaverse a Virtual Universe Parallel to Real Universe

-
Image
Metaverse is a new word that is made up of a combination of two words “Meta” and “Universe”, where Meta means beyond and “Verse” refers to Universe.  This term first debuted in Neal Stephenson's 1992 novel "Snow Crash," in which the protagonist, a pizza delivery man, hangs out in an online virtual fantasy world. WHEREAS "Ready Player One," a novel set in the Oasis Metaverse, updated the concept in 2011. There are many movies based on this concept that elaborately show the function of the Metaverse and give an idea of that too like Ready Player One and Avatar etc.  What is Metaverse? I t is a virtual 3D world created for interaction, doing business, and forging social connections using immersive, interactive, and collaborative technology in the field of the Internet. This is the world in which peoples interacts with each other through their respective Avatars using the metaphor of the real world but without its physical limits. Immersive technology is an integrat...
Read more

Keyboard Types, Layout & Technology

-
Image
What is Keyboard? Keyboard is an input device attached to a computer. This is used to print the text, numbers and special characters on screen in a document. What we are using today is a refined design of yester year’s research and development.  There are lots of improvements happen from its invention to development keeping in user’s point of view and ease. Now keyboard is most useful peripheral device of a computer. History of Keyboard  The invention of this wonderful device based on tele printer and keypunch machines. Christopher Latham Sholes was an American Inventor who invented the QWERTY keyboard. He was the first man who invented the first commercially used typewriter. He was born in Mooresburg, Pennsylvania on 14th February 1819. His design is the base of development in the field of keyboard. The first computer ENIAC was based on a punch card reader as its input and output device. But after 2years another computer BINAC was developed that was designed to use electro-me...
Read more