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 forget about the proper placement
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

Indian Operating Systems | Operating System-BharOS & BOSS

-
Image
BharOS an Operating System for Mobile   BharOS is an operating system developed for mobile by J and K Operations Private Limited under the aegis of IIT Madras Pravartak Technologies Foundation, a non-profit organization established by IIT Madras. It is based on Android open-source project. It is an Indian Government funded project for use in government and public systems. In other word BharOS is a Linux-Kernel based operating system quite similar to Android with some differences. It is initially released on 24 January 2023.    Some advantages of this system are as follows: It is not pre-installed with any application. Therefore, user is free to download the trusted applications from private application store services (PASS). It does not include google services. It supports Over the Air (OTA) updating technology. OTA means Over the Air Technology to update installed software, firmware and software bugs instantly wirelessly. This is the efficient way to upgrade a system sitting at one pl
Read more