+ Reply to Thread
Results 1 to 13 of 13

convert words to numeric value

  1. #1
    Registered User
    Join Date
    07-17-2013
    Location
    Hyderabad, India
    MS-Off Ver
    office 365
    Posts
    51

    convert words to numeric value

    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.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: convert words to numeric value

    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

  3. #3
    Registered User
    Join Date
    07-17-2013
    Location
    Hyderabad, India
    MS-Off Ver
    office 365
    Posts
    51
    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.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: convert words to numeric value

    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

  5. #5
    Registered User
    Join Date
    07-17-2013
    Location
    Hyderabad, India
    MS-Off Ver
    office 365
    Posts
    51
    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.
    Attached Files Attached Files

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: convert words to numeric value

    You must be joking! The reason
    we use words is to convey a
    proper meaning
    you will need magic to do that

  7. #7
    Registered User
    Join Date
    07-17-2013
    Location
    Hyderabad, India
    MS-Off Ver
    office 365
    Posts
    51
    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.

  8. #8
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: convert words to numeric value

    I want a formula to convert any available words into numeric values irrespective of how they are spelt
    if you could check those spellings and correct them. something like in your sample
    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

  9. #9
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: convert words to numeric value

    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
    Attached Files Attached Files
    Last edited by martindwilson; 08-28-2013 at 05:04 AM.

  10. #10
    Registered User
    Join Date
    07-17-2013
    Location
    Hyderabad, India
    MS-Off Ver
    office 365
    Posts
    51

    Re: convert words to numeric value

    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.

  11. #11
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: convert words to numeric value

    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.." ?

  12. #12
    Registered User
    Join Date
    07-17-2013
    Location
    Hyderabad, India
    MS-Off Ver
    office 365
    Posts
    51
    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.

  13. #13
    Registered User
    Join Date
    07-17-2013
    Location
    Hyderabad, India
    MS-Off Ver
    office 365
    Posts
    51

    Re: convert words to numeric value

    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...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Convert words into a numeric value
    By matric in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-29-2012, 01:56 AM
  2. Replies: 1
    Last Post: 02-25-2010, 06:40 AM
  3. [SOLVED] how do I convert numeric data to words?
    By azohawk in forum Excel General
    Replies: 3
    Last Post: 06-14-2006, 09:15 PM
  4. [SOLVED] convert numeric value to words, error msg!
    By HHTrang in forum Excel General
    Replies: 2
    Last Post: 04-13-2006, 03:50 AM
  5. [SOLVED] is there any function to convert the numeric number to words
    By new.microsoft.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-25-2005, 05:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1