I have been working on a small piece and I came across one requirement that in letters to customers in addition to amounts reported in numbers it must also include amounts receivable in words. Well I wasn’t paying attention to that part in the beginning. It don’t even cross my mind that there is no simple solution to do that in Excel. Wait! What? Really? Not even a formula? No… well yeah… but not exactly.
There is one formula in Excel which does convert numbers to text. BAHTTEXT function. Well I can see a big grin on your face right from here. But it comes with a bad news. This function in excel does convert numbers to words but in Baht language. Yes! not English! And if you don’t know Baht language you have no idea what this is about. You cannot even guess.
It would have been a big sigh if we had a way to translate the output if Excel developers cannot provide us with function to do the same. It is just beyond me that if it is there in Baht text then why not in English!
So does excel comes with any translation facility? NO!
Darn! So are we back to square one! NO!
We can use third party translation software to do the job. Here is what I did.
I extracted customer’s name with their amounts in two columns. In the third column applied the BAHTTEXT function and got the output in Baht language. Trick starts from this point forward.
Step 1: As you have the text in different rows. Select all the rows containing baht text
Step 2: Open your browser and get to any online translator. In my opinion Google Translator is good so I resort with it.
Step 3: Paste all the data. If your data is big then you can either translate the data in parts or first transfer the data to file and then use the file to get the translation.
Step 4: Copy the translated text from translator and paste it excel in the next column.
Step 5: Stop worrying!
My data contained well over 15000 entries and I used the simple web google translator without even a file. I translated the data in bits of 2000 entries each time and it did in no time. I did sample check the entries and they were very accurate.
It took me less than five minutes to do all that . But it was noob proof process. There are ways to get this done via VBA as well but not many know VBA so this solution will work for them I hope.
Caution: While using this method you need an internet connection with decent speed. Mine is turtle speed in terms of upstream but it still did the job for me. Avoid pasting the whole meat once as it may clog the RAM of your PC and also I observed Google Translator don’t work for all the entries. For me it didn’t processed more than 3,000 entries in a go. You can try your luck though 🙂 Also if your numbers are in cents or pennies or paisas than you are almost out of luck to use this workaround. I was lucky because I was allowed to round the number to near whole number. Wait for VBA based solution for this which I will post in next few days.
Share your ways of converting numbers to works in the comments below will love to learn new ways to go around this. And please can somebody raise a cause or something so that Excel developers can provide us this function in English!