Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Calculation to Convert Text to Number (Excel 2003)

    In column a2:a10 I have a question (Based on a survey question).
    The answer to the question is in B2:B10. The answer to the question can be Strongly Agree, Agree, Sometimes, Almost never, or never. (These are answers to the survey questions that are in A2:A10.
    I need a formual in C2:C10 that will convert the text to a number. I want Strongly agree answers to show up as 5. Agree show up as 4, Sometime show up as 3, Almost never show up as 2, and Never show up as 1.

    Thanks in advance!!!

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Calculation to Convert Text to Number (Ex

    You can use a lookup table to do this. You then use can use the VLOOKUP() function. I've included a named range version of the function and a cell address version of the function on the attached sheet.

    The formulas would be

    =VLOOKUP(B2,Table,2,0)
    =VLOOKUP(B3,$H$2:$I$6,2,0)
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need Calculation to Convert Text to Number (Ex

    1] Col B, enter : Strongly agree, Agree, Sometimes, Almost never, or never

    2] Then, C1 enter formula and copied down :

    =FIND(MID(B1,2,1),"elogt")

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Need Calculation to Convert Text to Number

    Whilst I agree that that will work, in terms of comprehensibility and maintainability I think a table option is better! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need Calculation to Convert Text to Number (Ex

    And, if you're going to depend on specific answers to the questions (as you've indicated), it would probably be a good idea to use Data | Validation. Otherwise, a misspelled word, like "Strangely Agree" will result in the wrong score. I've re-attached your sheet so you can see how data validation works. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Attached Files Attached Files
    - Ricky

Posting Permissions

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