Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

MS Excel 2007 Vlookup to convert text to numbers

Posted on 2015-02-11
Medium Priority
94 Views
I have a Excel spreadsheet of survey results with roughly 100 record results. I have several array of questions/answers in one spreadsheet, so I'm thinking the VLOOKUP is my best approach; however I don't know how to set it up. I will focus this though on the first question asked on the survey.

The first question results are in F - O. The range of answers are :
Not Interested
Not Now
Not Sure
Somewhat Interested
Very Interested

I would like to have these converted to numbers:
Not Interested = 1
Not Now = 2
Not Sure = 3
Somewhat Interested = 4
Very Interested = 5

0
Question by:eossma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 6

LVL 7

Accepted Solution

Katie Pierce earned 2000 total points
ID: 40603720
Have you considered just entering "helper columns" with IF formulas to create the numbers?

You could insert columns (rows?) next to (or below--sorry, was unclear on the arrangement of your data) what exists.  Then use the formula =IF(F2="Not Interested",1,IF(F2="Not Now",2,IF(F2="Not Sure",3,IF(F2="Somewhat Interested",4,IF(F2="Very Interested",5,"")))))

You could also copy and paste the values after doing that if you just wanted the #s.
0

Author Comment

ID: 40603803
Could I insert a column after O, and use something like:
=IF(F2="Not Interested",1,IF(F2:O2="Not Now",2,IF(F2:O="Not Sure",3,IF(F2:O="Somewhat Interested",4,IF(F2:O="Very Interested",5,"")))))
0

Author Comment

ID: 40603807
I mean O2 on all IFs.
0

LVL 7

Expert Comment

ID: 40603815
Yeah! That ought to work!  Feel free to upload the sheet if you have any problems and I can take a look for you.

Hope it goes well!
0

Author Comment

ID: 40603824
I get a #VALUE! error with:
=IF(F2:O2="Not Interested",1,IF(F2:O2="Not Now",2,IF(F2:O2="Not Sure",3,IF(F2:O2="Somewhat Interested",4,IF(F2:O2="Very Interested",5,"")))))
0

LVL 7

Expert Comment

ID: 40603840
Oh, sorry, I didn't look closely on the formula--the IF statement can't have a range (F2:O2).  That's why I suggested helper columns-- P - Y.

If you wanted all the data consolidated into one column, I'd need to take a look at the setup.  Can you upload a sample?
0

Author Comment

ID: 40603848
I'm attaching a modified version of the spreadsheet without names, so now the formula column sits in "O".
0

LVL 7

Expert Comment

ID: 40603930
0

Author Comment

ID: 40603955
attached.
0

LVL 7

Expert Comment

ID: 40603964
Still not attched
0

Author Comment

ID: 40603991
attached.
Survey.xlsx
0

LVL 7

Expert Comment

ID: 40604017
OK, you'd need a helper column for each column that has data.  See attached.

Column O corresponds with Column E, Column P with Column F, etc.
Survey2.xlsx
0

Author Closing Comment

ID: 40604043
Thank you!
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I finâ€¦
This collection of functions covers all the normal rounding methods of just about any numeric value.
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 â€“ â€œskill leveâ€¦
Suggested Courses
Course of the Month10 days, 6 hours left to enroll