Hi experts,
I'm at the beginner level in excel and my question is how can I convert a figure in words to numeric value.
Ex: one hundred to be converted to 100.
Searched google but couldn't find the solution to this.
Hi experts,
I'm at the beginner level in excel and my question is how can I convert a figure in words to numeric value.
Ex: one hundred to be converted to 100.
Searched google but couldn't find the solution to this.
Last edited by vlady; 08-27-2013 at 02:06 AM.
well depends on how you spell them and how high you want to go
what are 101 ,31 spelt like
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Thank you for your response.
I want a formula to convert any available words into numeric values irrespective of how they are spelt.
If any formula available for conversion pls. provide it.
Your best (only) option is to make a table of all the words, including all their possible misspellings (), and use a vlookup formula.
You provide the list; we provide the formula...
Last edited by protonLeah; 08-27-2013 at 01:54 PM.
Ben Van Johnson
PFA sample sheet that contains words needed to be converted to numeric value.
I am providing only few as example.
Pls. help me on how to convert.
You must be joking! The reason
we use words is to convey a
proper meaning
you will need magic to do that
Sir I'm not joking. As I said that i'm @ the beginning level in excel and when asked the same question to someone who is an expert as u all are, replied to find it on my own.
So, thought to get the result from this forum.
if you could check those spellings and correct them. something like in your sampleI want a formula to convert any available words into numeric values irrespective of how they are spelt
A6 = hundred becomes one hundred
the udf by Jerry Latham, MVP that can convert those words "respective on their spelling and how did those words were written.
WordsToDigits_SampleFile.xls
Note: Notice A5 and A9 as example of what it can not handle (spelling and incomplete words)
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
As i said words are spelt a certain way for a reason, that's why we go to school so everyone uses the same spelling just putting hundred is wrong
sixty one is often sixty-one
one hundred and one is one hundred one in some places that think they use English but don't really
Apart from that here is a workbook that does 0-10000 hoping that not everyone who uses excel can't spell
Last edited by martindwilson; 08-28-2013 at 05:04 AM.
Thank you vlady for your effort in providing the formula that I was looking for.
I want the formula to run in other worksheets as well.
I copied the vba code and tried to run the macro in new workbook but its not working?
Can u suggest me what to do in this case.
Why not use the formula of martin as long as you know what he is pointing to you in his previous posts.
With regards to the udf, are you sure you copied also what is in the module and the vba in sheet1.
You have saved the workbook as "macro enabled.." ?
Will work on Martin's attached file and get back.
In case of udf, I just copied the code which was displayed after pressing alt+f11 key and then opened a new workbook and used the same alt+f11 and pasted the copied code in the space and saved.
Now will follow the procedure what u have suggested and give my feedback.
Thank u all for your support.
Hi all.
The files of vlady and martin are working as expected.
Thankyou once again for your efforts.
Will get back with a new problem when I start working in-depth with excel files.
Till then ciao...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks