turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 157K Members
- 2,655 Online
- 38.6K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- How to CONVERT Numbers to Words with Built-in Excel Formula?

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

SOLVED
Home
## How to CONVERT Numbers to Words with Built-in Excel Formula?

- Home
- :
- Excel
- :
- General Discussion
- :
- How to CONVERT Numbers to Words with Built-in Excel Formula?

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-07-2018 07:10 AM

I am writing cheque and invoices that must have the dollar amounts typed in words and it is hectic to type the numbers and then type words manually. For example $541,125.57 should convert to five hundred forty-one thousand one hundred twenty-five Dollars and fifty-seven Cents

I cannot use Macros/VBA, the only solution to work for me is built-in Excel formula or anything that would not use Macros.

Thanks.

4 Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-07-2018 07:21 AM - edited 03-07-2018 07:24 AM

SolutionI had seen this question quite often, from colleagues, Q&A forums etc, but while there were many UDF solutions, there wasn't any solution with Built-In Formula (at least not to my knowledge).

You lucked out, as I had already written this Formula couple of years ago.

Here is the formula. I have also uploaded the file with formula.

It only has one limitation, It cannot convert more than number $999,999,999,999.00 "Nine Hundred Ninety-Nine Billion Nine Hundred Ninety-Nine Million Nine Hundred Ninety-Nine Thousand Nine Hundred Ninety-Nine Dollars and Zero Cent"

But I think it will do the job for you.

=IF(A1<=0,"Referred Cell Must be Empty",IF(A1>10^12-0.01,"Exceeds Maximum which is 999999999999",TRIM(PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(A1>=10^9," billion ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hu ndred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0, 12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "),CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety "),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),IF(VALUE(MID(TEXT(INT(A1),REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(A1),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," ")&IF(FLOOR(A1,1)>1," dollars"," ")&IF(AND(A1>=1,A1<2),"dollar",""))&" and "&IF(ISERROR(FIND(".",A1)),"Zero Cent",PROPER(IF(LEN(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2))=1,CHOOSE(1*LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2),"ten","twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&" cents","")&CONCATENATE(CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen")&" cents","twent y","thirty","forty","fifty","sixty","seventy","eighty","ninety"),IF(VALUE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine")&" cents",IF(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)="00","zero cent",IF(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)="01","one cent",IF(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),1)="0",CHOOSE(MID(TEXT(INT(LEFT(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",255)),255,200)),2)),REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine")&" cents",""))))))))))

- Tags:
- Excel formulas

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-07-2018 07:40 AM - edited 03-07-2018 07:41 AM

Hi Flora,

In fact, there is no built-in function to do that.

I do not think this can easily be achieved using a formula.

But always there is a solution with VBA/Macros.

Please let us know why you can not use the VBA/Macros?

Is it disabled on your computer?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-07-2018 07:47 AM

Wow!

THANK YOU SO VERY MUCH!

THANK YOU SO VERY MUCH!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-07-2018 07:51 AM

Haytham,

Due to corporate domain environment administration setting, macros are blocked by default in our office domain.

I got an excellent solution with built-in formulas provided by Jamil Mohammad.

Thanks

Due to corporate domain environment administration setting, macros are blocked by default in our office domain.

I got an excellent solution with built-in formulas provided by Jamil Mohammad.

Thanks

Related Conversations

Linking OneDrive to New Microsoft Teams Channel

Kelly Charlton
in
Microsoft Teams
on
01-15-2018
3,167
Views

1 Likes

3 Replies

Allow or Block Guest Users from a Specific Team in Microsoft Teams

Sam Cosby
in
Microsoft Teams
on
03-26-2018
3,086
Views

11 Likes

7 Replies

Adding an Existing OneNote notebook to Teams

CC Adeyemo
in
Microsoft Teams
on
02-02-2018
3,014
Views

0 Likes

18 Replies

Create private channel in a team

Catalin Prata
in
Microsoft Teams
on
11-29-2016
28.2K
Views

17 Likes

84 Replies

Fall Creators Update and Skype Room System

John Love Jr.
in
Skype for Business Users
on
02-14-2018
313
Views

0 Likes

1 Replies

How to use an existing Office 365 Group in Teams?

Jedidja Bourgeois
in
Microsoft Teams
on
11-30-2017
1,845
Views

0 Likes

5 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft