This is absolutely amazing! I did not find any solution without VBA until now. I can just imagine the amount of testing Pete had to do for this insane formula to work. Thank you!
Wow! Amazing! Thank you. Just two things come to my mind now. 1. Sharing is caring. 2. One need not re-invent the wheel again. Thank you Peter Menhennet for sharing the wheel so that others need not re-invent!
@@LeilaGharani I want to use the formula you generously demonstrated but I can't copy the gigantic formula. Please I need help.... this is my mail junrey.quilicot2@gmail.com
Leila!!!! Did you say this giant huge mind blowing formula was created by a construction professional? Amazing!!! Pete, you have raised my hopes.... Excel does magic in all industries, not just for computer and accounting professionals...... I am highly impressed
Ok, at this point I am very certain Leila developed the Excel program and maintains it. I don't think any Microsoft Excel programmer can match her skills. Thank you very much!
I salute you and bow to you out of sheer respect. Thank you Mr. Peter Menhennet for sharing and thank you Ms. Leila Gharani for propagating this awesome formulae. God bless you!
What the kind of expertise you have in Excel.... Pete... Great...I appreciate the degree of dedication in testing such a lengthy formula.... Thanks both for sharing such a useful formula.
Dear Leila, Please convey a heartfelt thank you to Pete M for this wonderful, wonderful formula. He must have spent days to perfect it. Salute to his patience. Thank you to you too for taking the initiative for sharing the same. Regards,
Crikey! I did something similar but using an array a few years ago and getting the logic right was, to say the least, mind boggling. My head still aches at the thought. Getting it into a single formula would have blown all the fuses, so 👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏 Pete, let me guess. Contractor's payment claims? If the bottom line in numerals doesn't exactly match the words then the client tosses the claim back and the contractor doesn't get paid. It's bad enough if everyone is speaking one language but when the engineer is English, the contractor is Afrikaans and the client is isiZulu you surely do need this formula. Thanks Pete, where were you when I needed you...?
It's impressive, no doubt. Looping through the characters in any string - numbers or letters - is extremely difficult to achieve in Excel. Well done Pete. Well delivered, too, of course!
This was one of my concerns that used to share everywhere. Thank you for this effort. But I still believe this should be part of inbuilt. Another concern I always express is that, adding a name to the grouping the data. The reader should know, without expanding the data columns/rows, what is grouped. Excel should allow adding a name option while grouping the data.
The student becomes the master. Been looking for years for this solution. If there was a Nobel prize for excel formulas. Pete would definitely get my vote. 👍 Pete the legend
@@LeilaGharani Thanks for replying and for sharing the gem. There was something I wanted to bring up. I am on a Mac using the latest Office 365. I struggled to get the formula working. First , “command A” wasn’t selecting all. Then when I pasted it in a new document. It wouldn’t paste as a formula but as text and was incomplete, no paste special configurations would work. After about an hour of frustration, as only the Billions formula seemed to copy. I fixed this by comparing the Billions formula with the other two and noticed some differences in the spaces. I went into the formula bar, deleted all the spaces where the formula started on a new line instead of continuing. Copied it all by highlighting as I still couldn’t select all and then got it to paste in a new spreadsheet. changed my currency to KWD and 000/1000 as we use three decimals here. Hope this helps anyone that runs into the same problem. I can only imagine how frustrating it must have been to come up with this. I can’t thank you enough for sharing.
It must have taken hours for creating this formula. I don't think I have the stamina to write it down so I will just copy and paste it. Many thanks for video.
Ms. Gharani, the videos you make have significantly helped me in my career. I've watched your videos for that past few years and they have given me a step up on others. Thank you for these videos.
That is cool. I also had a student back in 2003 show me a similar formula, but this mathematician had a formula that centered around the MOD function to accomplish a single cell number to text formula. It NEVER seases to amazing me what humans can do with Excel : ) Thanks for the video, Teammate!
@@LeilaGharani This was a Seattle mathematician who was NOT registered in my class and come into my office hour to show it to me back in about 2003. I am not sure if I still have the workbook he showed me. I still have all the folders of files from my classes from way back then, but I am not sure if the file he showed me is in one of them. I will look...
for those working with a European formatting system, make a search and replace in the formula for "0.0" into "0,0" so you have the proper European decimal separator...
It has been almost over 3 years when i saw it first time. Since then "The Amount Word Solution" is always inspired me; "How Genius Mr. Pete is" ---- Bundle of Appreciation & Respect for him.
Wow!!! Hats of for coming up with this, let alone sharing it. As a side note, shame on Microsoft for not making this a default formula for a long time.
Heartly Thanks to Mr PETE for this wonderful formula creation and also thank to Ms Leila to bring this up through your videos. It makes simple work now
Sincerely thanks to Pete, Leila, Jim M., Zafar Khan and Abdul Rahman Mohammed for creating and sharing this amazing formula. With Leila's explanation and everyone hardwork, I successfully further modify the formula to get my desired result. Thank you once again.
Mam really I am searching this formula, I could not seach it today my searching is end, from you I got very amazing trick, thank you from bottom of my heart. God bless you, thank you mam...
A bit complex at first sight but after a simplified view by Leila it perfectly makes sense... Thanks Peter for the hard work and sharing this and thanks Leila for the explaination.
Amazing, I once built a formula in older version of Excel with use of nested ifs, though there wasn't any choose then. The formula was just about long like this.
@@LeilaGharani Its not only about the amazing usefulness; but there is so much to learn from this integrated function. I may need to modify this just a little to fit in Indian context; where 1] the currency symbol is prefixed 2] for numbers after decimal and 3] use of "and" which comes between Rupees and Paise. Thanks a lot. You're a billion dollar girl :)
Leila Gharani This is so amazing...!!! This is the first time I have ever seen in excel history in my life, thank you for share your knowledge with us.
wonderful. I converted this formula to display just the word of the number. 2314 = two three one four. Up to a 9 digit number. 9 lines of repetition, but super easy to read. I also put a If(count(A1)=1 then .... to ensure it is a number.Thanks
I was struggling with this formula. I replaced B3 with J7 for my workbook and it was showing the formula text so I tried again and it was showing zero dollars and cents even with the formula showing J7. I realized that I was referencing the work book I copied from. So to ensure I did repeat the mistake I copied the formula to Word and them pasted from word to my Excel workbook. It works! I was getting ready to try VBA.
Hi Pete... Great work! and I salute! I high appreciated its a big help. Thank you for sharing this. Thank you also Ms Leila for the clear explanation on how it works. By the way I added the LET function so that it only refences one cell.
Leila, Microsoft Word has an easy function to convert dollar amounts to text. For example, if you had a table 1X2, then you can enter the dollar amount in R1C1, and in R1C2, you can insert a field with the following format { =R1C1 \* DollarText \* Caps}. So, if you enter $3,501.45 in R1C1, Word will convert that to Three Thousand Five Hundred One And 45/100. The first switch \* DollarText makes the conversion from number to text in all lowercase. The second switch \* Caps capitalizes the first letter of each word. To insert a field in Word, you press CTRL+F9, and then just enter the appropriate formula.
Oh my freaking goodness. Thanks for this info. I was struggling so much with the VBA thing damn!. Even copying from MS website and still doesn't work. You guys rock!
thank you so much for the formula, a bit hard to find such thing when people usually give the vba route.. one thing to note though, if you use comma as decimal separator, then you should find and replace all the periods in the formula into commas.. also, make sure that the separator used in the formula is matched with what you're using in your excel.. that's all, thank you very much once again!
Thank you Mr. Pete and thank you Ms. Leila.... For me also this was a nightmare for a long time. Then I found a solution myself by using dget function. Now it has become more easy
At 3:40.. You explain how to change B3 to B4.. It can be easier.. Click on Cell B3 on the edge, you see arrows to move.. move cell b3 to B4. And all the references in the formula moves with it. After that, its easier to type new input. Always nice tutorials and learned lot .. Tnx and keep on :)
Thank you PETE! thank you Leilah. Has anyone in the community done this in Spanish. I'm trying to modify it myself, but Spanish is a very rich language. 500 + 26 = Quinientos Veintiséis
