I've been looking for something like this for months. A few days ago I gave up and looked to migrate all the info I was using to Excel and had to try a super weird formula to substitute Query (with success). But this opens a lot more possibilities and makes my life more simple. Thank you so much!!
Thanks for sharing this awesome video and great library. I guess we can take a look at the js code as well and see how it is processed. Got 'formula only' version too? Not really sure if that's possible but that would be better, right? Stay safe and help us God get through this pandemic. More power to you folks!
Thank you again extremely helpful - can this be used in an Arrayformula? somehow I cant get it working =ArrayFormula({"MED1T";IF(K2:K"", num2t(M2:M),)})
Is it possible to function certain words into different words of my choice? For example I have Apple, Orange and a Pineapple, is it possible if I wrote Apple it will present it as "Red" and Orange into "Orange"?
Hello Dear, thank you for this but actually, I tried millions of times without success, could you please share with us the google sheet you worked on in order to make a copy and work directly on your personalized version, thank you very much for your understanding and support.
HI!. Your video was very helpful!! Thank You So Much!! Can you please tell a way to remove those Dashes - The " - " sign from those figures?? Please help. Thank You
@@ExcelGoogleSheets Hi!! I didn't try your above said method, but I reverse engineered the code, and changed the 206th line of code from "-" to " " (Giving just one space between the quotes). It worked. Anyway, I value your response. Thank you for your help!
If I wish to format number (ex. 12.34) similar to TEXT(12.34, "#0.00") in app Script? BTW - I admire Your job, all is planned, soryed, tagged.... I feel here almost like on my own PC.
About cents, as I remember about writing cheques, cents are not converted to words, rather just write the number then /100, eg: 45/100. But for other purposes where cents need to be converted to words, then same function can be used plus "cents" or it's equivalence in other languages, eg Spanish: "cuarenta y cinco centavos"; English: forty five cents. Usually, that is when "and" is required, eg 123.45 converts to "one hundred 'x' twenty three and forty five cents", where 'x' depends on "noand" value. Then again, other places may have different cents handling, so better confirm what's required. To summarize, cent parameters could be: cents - true or false prefix - ' and ', ', ', 'x' centnum - true or false suffix - true or false Looks complicated, maybe the reason why it's excluded :) Thanks for sharing this video and library. Cheers!
As I was recording this video it occurred to me that this function converts numbers to text, not currency to text. So 123.45 would be "one hundred twenty three point forty five", there are no dollars or cents. Changing this to make it output dollars and cents is not that complicated in English. In English It's One Dollar and the rest are all Dollars, ex. 101 Dollars. I'm not sure how this works in other languages though.
@@ExcelGoogleSheets Cents and decimals I guess needs their own functions. Cents for currency may have default 2 decimal places. There are currencies though that don't have cents, like Japanese Yen, Indonesian Rupiah, etc... eg, 1 U$ = 0.000064 Rupiah as of this writing. So if we have around $64, we're millionaires out there - hooray! Meanwhile, decimals may have following parameters: Length, eg: x.1 or x.12 or x.123, or x.123456, etc... Rounding, eg: 1.5 = 1 and 2.5=2 or 1.5=2 and 2.5=2 I guess we can stick to integers for this session. makes life easier for all of us :) Cheers!
@@richardmhain Can you add the formula with cents for me please? What would I need to add and where should I put it, so I can get the cents in the correct spot so I can have the correct function. Thank you this is my function so far function Test() { let result = writtenNumber(1234); console.log(result) } function NUMBERTOTEXT(Num,lang = "en", noAnd = false) { let result = writtenNumber(Num, {lang: lang, noAnd}); return result; } Execution log 11:11:59 AM Notice Execution started 11:11:56 AM Info one thousand two hundred and thirty-four 11:12:00 AM Notice Execution completed
Dear Sir, Thank you so much for this. You are great. I have been trying to get this for milleniums. I watch and studied nearly all your videos. One problem though. I got the decimal (cent) part working by using "right", "len", and "search" functions. But the problem is if the decimal is equal to or greater then .50, this rounds up to 1. e.g. 106,000.54 ====> One Hundred Six Thousand One and Fifty-four cents. The only round up code I can find is in line 1713 "n = Math.round(+n); If I comment that line, the result gets "ERROR". Is there any fix you can think of. I really appreciate your suggestion.
@@ExcelGoogleSheets Dear Sir, Thank you so much for your prompt reply. I really appreciate it. My problem is, I don't want to roundup the decimal numbers. The library code is rounding up the decimal numbers to whole numbers if it finds decimal. Here is what I have and what function I used and the output of the function: In cell "E9" =====> "US Dollars" In cell "B15" =====> "106,000.50" The function I used is: =if(RIGHT(B15, len(B15)-(search(".",B15)))="0",E9&" "&Proper(NUMBERTOTEXT(B15))&" only.", E9&" "&Proper(numbertotext(B15)&" "&numbertotext(RIGHT(B15, len(B15)-(search(".",B15))))&" cent only.")) The result is: "US Dollars One Hundred Six Thousand And One Fifty Cent Only" I hope this clear things up. This would not only help me. I hope this will help others who are looking for this decimal issue. I slute you sir.
function Test() { let result = writtenNumber(1234); console.log(result) } function NUMBERTOTEXT(Num,lang = "en", noAnd = false) { let result = writtenNumber(Num, {lang: lang, noAnd}); return result; }
@@ExcelGoogleSheets I am also talking about English version. For example, if I enter 12345 it results correctly as Twelve thousand three hundred and forty-five. But as I enter a six digit number 123456 it spelled as one hundred twenty three thousand four hundred fifty-six.
When I add this code it retur with this message: Missing ) at line 3 function NUMBERTOTEXT(num,lang = "fr") { let result = writtenNumber(num, {lang: lang}); return result; }
function Test() { let result = writtenNumber(1234); console.log(result) } function NUMBERTOTEXT(Num,lang = "en", noAnd = false) { let result = writtenNumber(Num, {lang: lang, noAnd}); return result; }
@@ExcelGoogleSheets Ok, I found a way to get the word numbers in Italian. Now I just need to fix the last thing: to cancel the spacing between words. For example: From 1200 = "mille due cento" to 1200="milleduecento". Actually the first one ("mille due cento") is not correct in my language. :( Is there a code that I have to change to fix this issue? Thank you again!
If you just need to convert a small set of numbers to words I think this alternative is a good option (not have to use code): ruclips.net/video/_Y7c798qujU/видео.html
I've been looking for something like this for months. A few days ago I gave up and looked to migrate all the info I was using to Excel and had to try a super weird formula to substitute Query (with success). But this opens a lot more possibilities and makes my life more simple.
Thank you so much!!
And thanks for making these videos for free and specially with this worldwide crisis. Kind regards from Mexico
Great to hear!
Hello the function doesnt work anymore in 7 FEBUARY 2022. In the log function its still work, but in the sheet it's keep loading
Thanks! This is great for automatically creating documents.
thanks for providing us good videos on google sheet , you are doing great job
I see that at 3:15 you wanted to test it with cents. Were you able to do so? I need to add it with cents. Can you please help? Thank you
Thank you so much and thanks for the Github contributor
Thanks a lot for the tutorial! That is exactly what I was looking for!
Thanks for sharing this awesome video and great library. I guess we can take a look at the js code as well and see how it is processed.
Got 'formula only' version too? Not really sure if that's possible but that would be better, right?
Stay safe and help us God get through this pandemic.
More power to you folks!
Mi estimado, Usted se merece 10,000,000 de Suscriptores, Demasiada calidad en sus tutoriales
Extremely helpful. Was looking for it for a long time. Thank you!
It was Revolutionary for me.
Thanks for the great tutorial. Anyway to include cents (1234.)? Appreciate to share if you know the way.
Thank you! Very useful
Great job..! Thanks..💐👌
Thank you so much for the excellent clarification of the topic. I noticed that this function cannot be used on an array. Is it true?
Yes, but you can rewrite the function to also accept arrays if necessary.
Отличный урок! и наконец-то русский язык)
меня тоже очень порадовало :)
great! can you make a video about SORTN function? thanks
Super Cool Tool, I love it ❤️❤️❤️
How can I do with decimal?
same here
@@SyiarSunnahNabawiyahwere you able to find out how to do it with a decimal?
were you able to find out how to do it with a decimal?
Great, thanks!, i wish i had it for Excel selecting language
There is for Spanish, I bet there's somewhere for English. It involves VBA
Thank you again extremely helpful - can this be used in an Arrayformula? somehow I cant get it working =ArrayFormula({"MED1T";IF(K2:K"", num2t(M2:M),)})
very nice! Great, thanks!
super
what a genius
Is it possible to function certain words into different words of my choice?
For example I have Apple, Orange and a Pineapple, is it possible if I wrote Apple it will present it as "Red" and Orange into "Orange"?
Great! Gracias!
Hello Dear, thank you for this but actually, I tried millions of times without success, could you please share with us the google sheet you worked on in order to make a copy and work directly on your personalized version, thank you very much for your understanding and support.
wonderful
is there a way to do it in an array? because I got a lot of number rows to convert at once and I want it to be faster. Thank you in advance!
HI!. Your video was very helpful!! Thank You So Much!! Can you please tell a way to remove those Dashes - The " - " sign from those figures?? Please help. Thank You
Use SUBSTITUTE function around it and replace "-" with " "
@@ExcelGoogleSheets Hi!! I didn't try your above said method, but I reverse engineered the code, and changed the 206th line of code from "-" to " " (Giving just one space between the quotes). It worked. Anyway, I value your response. Thank you for your help!
If I wish to format number (ex. 12.34) similar to TEXT(12.34, "#0.00") in app Script?
BTW - I admire Your job, all is planned, soryed, tagged.... I feel here almost like on my own PC.
developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Number/toFixed
Please update sir, this function doesn't work again..
What about those language you watch not included?
Great!, now the second part including cents.
You should be able to use the same function by simply extracting the cents part out of a number and running the same function over it.
About cents, as I remember about writing cheques, cents are not converted to words, rather just write the number then /100, eg: 45/100.
But for other purposes where cents need to be converted to words, then same function can be used plus "cents" or it's equivalence in other languages, eg Spanish: "cuarenta y cinco centavos"; English: forty five cents. Usually, that is when "and" is required, eg 123.45 converts to "one hundred 'x' twenty three and forty five cents", where 'x' depends on "noand" value.
Then again, other places may have different cents handling, so better confirm what's required.
To summarize, cent parameters could be:
cents - true or false
prefix - ' and ', ', ', 'x'
centnum - true or false
suffix - true or false
Looks complicated, maybe the reason why it's excluded :)
Thanks for sharing this video and library.
Cheers!
As I was recording this video it occurred to me that this function converts numbers to text, not currency to text. So 123.45 would be "one hundred twenty three point forty five", there are no dollars or cents. Changing this to make it output dollars and cents is not that complicated in English. In English It's One Dollar and the rest are all Dollars, ex. 101 Dollars. I'm not sure how this works in other languages though.
@@ExcelGoogleSheets Cents and decimals I guess needs their own functions.
Cents for currency may have default 2 decimal places.
There are currencies though that don't have cents, like Japanese Yen, Indonesian Rupiah, etc...
eg, 1 U$ = 0.000064 Rupiah as of this writing.
So if we have around $64, we're millionaires out there - hooray!
Meanwhile, decimals may have following parameters:
Length, eg: x.1 or x.12 or x.123, or x.123456, etc...
Rounding, eg: 1.5 = 1 and 2.5=2 or 1.5=2 and 2.5=2
I guess we can stick to integers for this session. makes life easier for all of us :)
Cheers!
@@richardmhain Can you add the formula with cents for me please?
What would I need to add and where should I put it, so I can get the cents in the correct spot so I can have the correct function. Thank you
this is my function so far
function Test() {
let result = writtenNumber(1234);
console.log(result)
}
function NUMBERTOTEXT(Num,lang = "en", noAnd = false) {
let result = writtenNumber(Num, {lang: lang, noAnd});
return result;
}
Execution log
11:11:59 AM Notice Execution started
11:11:56 AM Info one thousand two hundred and thirty-four
11:12:00 AM Notice Execution completed
cảm ơn bạn, tôi đến từ việt nam
Nice
I've been trying to run the function but gave me this message "Attempted to execute numbertotext, but it was deleted."
does anyone know why?
I have a question.... how could you apply this in an array formula? because it only works if you have the number beforehand
You will need to completely rewrite the function for it to work with arrays.
This in mobile can do
This trick not working on array formula . Please Help
How about with decimal?
Dear Sir, Thank you so much for this. You are great.
I have been trying to get this for milleniums. I watch and studied nearly all your videos.
One problem though. I got the decimal (cent) part working by using "right", "len", and "search" functions.
But the problem is if the decimal is equal to or greater then .50, this rounds up to 1.
e.g.
106,000.54 ====> One Hundred Six Thousand One and Fifty-four cents.
The only round up code I can find is in line 1713 "n = Math.round(+n);
If I comment that line, the result gets "ERROR".
Is there any fix you can think of. I really appreciate your suggestion.
Multiply the decimal number by 100, so 0.54 * 100 will be 54.
You can round up using ROUNDUP function in spreadsheet or you can use Math.ceil() in JavaScript
@@ExcelGoogleSheets Dear Sir, Thank you so much for your prompt reply. I really appreciate it.
My problem is, I don't want to roundup the decimal numbers. The library code is rounding up the decimal numbers to whole numbers if it finds decimal.
Here is what I have and what function I used and the output of the function:
In cell "E9" =====> "US Dollars"
In cell "B15" =====> "106,000.50"
The function I used is:
=if(RIGHT(B15, len(B15)-(search(".",B15)))="0",E9&" "&Proper(NUMBERTOTEXT(B15))&" only.", E9&" "&Proper(numbertotext(B15)&" "&numbertotext(RIGHT(B15, len(B15)-(search(".",B15))))&" cent only."))
The result is:
"US Dollars One Hundred Six Thousand And One Fifty Cent Only"
I hope this clear things up. This would not only help me. I hope this will help others who are looking for this decimal issue. I slute you sir.
@@aungthanoo347 instead of NUMBERTOTEXT(B15) use NUMBERTOTEXT(INT(B15))
@@ExcelGoogleSheets Thanks for your prompt reply. Let me try. It will work 😀.
How to get output in Indian format. (1 crore, 1 lakh etc., instead of millions & billions)
SyntaxError: Invalid or unexpected token (line 3, file "Code.gs")Dismiss
show this......please halp me
thank you, but how I can add currency to the code (dollar and cents)?
?? How
@@rawyabashir9804 =UPPER(NUMBERTOTEXT(E27))&" DOLLARS U.S." This will just add dollars at the end
How to add “only” in the end of each sentence?
hi, i just add somthing behind the code and it worked!
you can try on it
return (result)+" Only"
at the "NUMBERTOTEXT" script there
how can i fix this error? : Syntax error: SyntaxError: Unexpected token '!' line: 1 file: no to words.gs
function Test() {
let result = writtenNumber(1234);
console.log(result)
}
function NUMBERTOTEXT(Num,lang = "en", noAnd = false) {
let result = writtenNumber(Num, {lang: lang, noAnd});
return result;
}
Thanks a lot. But this is not functioning correctly for 6 digit or more digit numbers, plz check and reply
I can only check English version and it seems fine to me.
@@ExcelGoogleSheets I am also talking about English version. For example, if I enter 12345 it results correctly as Twelve thousand three hundred and forty-five. But as I enter a six digit number 123456 it spelled as one hundred twenty three thousand four hundred fifty-six.
Yes, isn't that correct?
Shafiuddin Ahmed try the Indian English option , then it will make sense to you
Learn Google Spreadsheets not in Indian number system
When I add this code it retur with this message: Missing ) at line 3
function NUMBERTOTEXT(num,lang = "fr") {
let result = writtenNumber(num, {lang: lang});
return result;
}
This works under V8 runtime. It looks like you might have an old project from Rhino runtime.
Learn Google Spreadsheets that’s right.
You can still use it, just don't use default NUMBERTOTEXT(num,lang){
function Test() {
let result = writtenNumber(1234);
console.log(result)
}
function NUMBERTOTEXT(Num,lang = "en", noAnd = false) {
let result = writtenNumber(Num, {lang: lang, noAnd});
return result;
}
Anyone knows if it is possible to do the same thing in Italian language? Thank you.
Try to wrap it in GOOGLETRANSLATE function and see if translations work for you.
@@ExcelGoogleSheets Thanks, I am going to try it!
@@ExcelGoogleSheets Ok, I found a way to get the word numbers in Italian.
Now I just need to fix the last thing: to cancel the spacing between words. For example:
From 1200 = "mille due cento" to 1200="milleduecento".
Actually the first one ("mille due cento") is not correct in my language. :(
Is there a code that I have to change to fix this issue?
Thank you again!
7:00 :)
If you just need to convert a small set of numbers to words I think this alternative is a good option (not have to use code):
ruclips.net/video/_Y7c798qujU/видео.html
ॾँटडतककरग
thanks, sir...work for me 👍🙏
i notice your video in the script.
Amazing! Thank you so much