Solved

MS Excel 2007 Vlookup to convert text to numbers

Posted on 2015-02-11
13
89 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
  • 7
  • 6
13 Comments
 
LVL 7

Accepted Solution

by:
Katie Pierce earned 500 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

I wanted to share this with fellow Experts, who might not know. How often have you wanted to learn something, only to be set back by either restrictions imposed on "trial" or "evaluation" software?  How often have you had to rebuild a home networ…
This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

828 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