# Thread: Need Calculation to Convert Text to Number (Excel 2003)

1. ## 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. ## 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)

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

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

#### Posting Permissions

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