Skip to content Skip to sidebar Skip to footer
Home / Game

2+ How to Convert Numbers to Letters in Excel (for Beginners)

By Zerina Post a Comment

Do you know if it’s a formula or an Excel spelled function? Well, the formula or function is used to convert numbers into letters or sentences automatically. For example, in making a receipt, which usually mentions a sentence calculated from the nominal rupiah, such as changing Rp. 600,000, – to six hundred thousand rupiah.

In general, the method commonly used for this Excel function is to use the VBA macro code or use ADDIN, which is Excel.

However, in fact, the use of VBA code or Addin functions is spelled out in Excel, which has several drawbacks, namely:

  1. If you use an addin, it’s possible that the function won’t work if you open it on another computer.
  2. If you use VBA code, novice (new) users will find it difficult to use it.
  3. If using VBA code, files cannot be saved with .xlsx extension.
  4. And others.

The solution is that we can use a combination of several excel functions to make formulas calculated without using VBA code and or Addin spelled out Excel.

How to Convert Numbers to Letters in Excel

In this tutorial, we’ll study two types of Excel countable formulas. First, to say rupiah is like changing the number 2,300,000 to Two Million Three Hundred Thousand. Second, to convert numbers to words like 90,80 to Ninety Point Eight Zero.

1. Excel Count Formula for Rupiah

Here we will convert numbers into sentences. Notice the example below:

How to Convert Numbers to Letters

The formula in the example above is:

=IF(A2=0,"nol",IF(A2<0,"minus ","")& SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( IF(--MID(TEXT(ABS(A2),"000000000000000"),1,3)=0,"",MID(TEXT(ABS(A2),"000000000000000"),1,1)&" ratus "&MID(TEXT(ABS(A2),"000000000000000"),2,1)&" puluh "&MID(TEXT(ABS(A2),"000000000000000"),3,1)&" trilyun ")& IF(--MID(TEXT(ABS(A2),"000000000000000"),4,3)=0,"",MID(TEXT(ABS(A2),"000000000000000"),4,1)&" ratus "&MID(TEXT(ABS(A2),"000000000000000"),5,1)&" puluh "&MID(TEXT(ABS(A2),"000000000000000"),6,1)&" milyar ")& IF(--MID(TEXT(ABS(A2),"000000000000000"),7,3)=0,"",MID(TEXT(ABS(A2),"000000000000000"),7,1)&" ratus "&MID(TEXT(ABS(A2),"000000000000000"),8,1)&" puluh "&MID(TEXT(ABS(A2),"000000000000000"),9,1)&" juta ")& IF(--MID(TEXT(ABS(A2),"000000000000000"),10,3)=0,"",IF(--MID(TEXT(ABS(A2),"000000000000000"),10,3)=1,"*",MID(TEXT(ABS(A2),"000000000000000"),10,1)&" ratus "&MID(TEXT(ABS(A2),"000000000000000"),11,1)&" puluh ")&MID(TEXT(ABS(A2),"000000000000000"),12,1)&" ribu ")& IF(--MID(TEXT(ABS(A2),"000000000000000"),13,3)=0,"",MID(TEXT(ABS(A2),"000000000000000"),13,1)&" ratus "&MID(TEXT(ABS(A2),"000000000000000"),14,1)&" puluh "&MID(TEXT(ABS(A2),"000000000000000"),15,1)),1,"satu"),2,"dua"),3,"tiga"),4,"empat"),5,"lima"),6,"enam"),7,"tujuh"),8,"delapan"),9,"sembilan"),"0 ratus",""),"0 puluh",""),"satu puluh 0","sepuluh"),"satu puluh satu","sebelas"),"satu puluh dua","duabelas"),"satu puluh tiga","tigabelas"),"satu puluh empat","empatbelas"),"satu puluh lima","limabelas"),"satu puluh enam","enambelas"),"satu puluh tujuh","tujuhbelas"),"satu puluh delapan","delapanbelas"),"satu puluh sembilan","sembilanbelas"),"satu ratus","seratus"),"*satu ribu","seribu"),0,""))," "," ")) &" rupiah"

The formula above is indeed very long and certainly confusing, but you don't need to worry because you only need to copy it. In the formula above, the numbers that will be converted into text/letters are calculated in Excel, namely cell A2.

In the example above, you have added the word rupiah to the output where you have added the following excel formula at the end of the excel formula above, namely:

&" rupiah"

In addition, you can also change the case of the Excel formula by using a formula where the cell reference is cell A2:

=PROPER(IF(A2=0,"nol",IF(A2<0,"minus ","")& SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( IF(--MID(TEXT(ABS(A2),"000000000000000"),1,3)=0,"",MID(TEXT(ABS(A2),"000000000000000"),1,1)&" ratus "&MID(TEXT(ABS(A2),"000000000000000"),2,1)&" puluh "&MID(TEXT(ABS(A2),"000000000000000"),3,1)&" trilyun ")& IF(--MID(TEXT(ABS(A2),"000000000000000"),4,3)=0,"",MID(TEXT(ABS(A2),"000000000000000"),4,1)&" ratus "&MID(TEXT(ABS(A2),"000000000000000"),5,1)&" puluh "&MID(TEXT(ABS(A2),"000000000000000"),6,1)&" milyar ")& IF(--MID(TEXT(ABS(A2),"000000000000000"),7,3)=0,"",MID(TEXT(ABS(A2),"000000000000000"),7,1)&" ratus "&MID(TEXT(ABS(A2),"000000000000000"),8,1)&" puluh "&MID(TEXT(ABS(A2),"000000000000000"),9,1)&" juta ")& IF(--MID(TEXT(ABS(A2),"000000000000000"),10,3)=0,"",IF(--MID(TEXT(ABS(A2),"000000000000000"),10,3)=1,"*",MID(TEXT(ABS(A2),"000000000000000"),10,1)&" ratus "&MID(TEXT(ABS(A2),"000000000000000"),11,1)&" puluh ")&MID(TEXT(ABS(A2),"000000000000000"),12,1)&" ribu ")& IF(--MID(TEXT(ABS(A2),"000000000000000"),13,3)=0,"",MID(TEXT(ABS(A2),"000000000000000"),13,1)&" ratus "&MID(TEXT(ABS(A2),"000000000000000"),14,1)&" puluh "&MID(TEXT(ABS(A2),"000000000000000"),15,1)),1,"satu"),2,"dua"),3,"tiga"),4,"empat"),5,"lima"),6,"enam"),7,"tujuh"),8,"delapan"),9,"sembilan"),"0 ratus",""),"0 puluh",""),"satu puluh 0","sepuluh"),"satu puluh satu","sebelas"),"satu puluh dua","duabelas"),"satu puluh tiga","tigabelas"),"satu puluh empat","empatbelas"),"satu puluh lima","limabelas"),"satu puluh enam","enambelas"),"satu puluh tujuh","tujuhbelas"),"satu puluh delapan","delapanbelas"),"satu puluh sembilan","sembilanbelas"),"satu ratus","seratus"),"*satu ribu","seribu"),0,""))," "," "))&" Rupiah")

The result will look like in the image below:
Numbered Formulas Proper Functions

Note! There are several things that you should pay attention to when using the excel formula above, namely:

  • Only displays a maximum of 15 digit numbers. So if the formula is more than 15 digits then it will not produce the calculated function that it should.
  • The Excel formula in the example cannot accommodate numbers after commas. So, if you need a number behind the comma then you don't use the above formula or separate the first number between the first comma and the back of the comma.
  • You can replace the comma (,) in the excel formula above to a semicolon (;) in Microsoft Excel depending on the settings in each user's Excel.

2. Excel Numerical Formulas Number To Word

The formula is calculated in excel numbers to words, meaning that we change numbers to letters or words. Take a look at the example of the excel formula for numbers to words below:

How to Convert Numbers to Letters for Beginners

The formula in the example above is:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(A2,"#,00"), 0,"Nol "),1,"Satu "),2,"Dua "),3,"Tiga "),4,"Empat "),5,"Lima "),6,"Enam "),7,"Tujuh "),8,"Delapan "),9,"Sembilan "),",","Koma ")

In the excel formula above, cell A2 is the cell containing the number to be changed.

Notes! If you want a number after the comma then change "#,00" to "#,0" or something else as needed.

So the discussion this time about how to convert numbers into letters with formulas spelled out Macro VBA and Addins in Excel. Hopefully useful and good luck. Thank you!

Article Resources

  • PROPER (PROPER Function) – Office Support | https://bit.ly/2y3Uzr9
  • SUBSTITUTE function – Office Support | https://bit.ly/2Y0LpLt
  • Manual Excel Formulas Without Macros – Excel Class | https://bit.ly/2y7NDto

You may like these posts :

Post a Comment for "2+ How to Convert Numbers to Letters in Excel (for Beginners)"