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
13
Medium Priority
?
94 Views
Last Modified: 2015-02-11
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

How would I go about this?
0
Comment
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
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 7

Accepted Solution

by:
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

by:eossma
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

by:eossma
ID: 40603807
I mean O2 on all IFs.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 7

Expert Comment

by:Katie Pierce
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

by:eossma
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

by:Katie Pierce
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

by:eossma
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

by:Katie Pierce
ID: 40603930
Sample did not upload
0
 

Author Comment

by:eossma
ID: 40603955
attached.
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40603964
Still not attched
0
 

Author Comment

by:eossma
ID: 40603991
attached.
Survey.xlsx
0
 
LVL 7

Expert Comment

by:Katie Pierce
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

by:eossma
ID: 40604043
Thank you!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question