Solved

MS Excel 2007 Vlookup to convert text to numbers

Posted on 2015-02-11
13
87 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

896 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now