• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 96
  • Last Modified:

MS Excel 2007 Vlookup to convert text to numbers

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
eossma
Asked:
eossma
  • 7
  • 6
1 Solution
 
Katie PierceCommented:
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
 
eossmaAuthor Commented:
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
 
eossmaAuthor Commented:
I mean O2 on all IFs.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Katie PierceCommented:
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
 
eossmaAuthor Commented:
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
 
Katie PierceCommented:
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
 
eossmaAuthor Commented:
I'm attaching a modified version of the spreadsheet without names, so now the formula column sits in "O".
0
 
Katie PierceCommented:
Sample did not upload
0
 
eossmaAuthor Commented:
attached.
0
 
Katie PierceCommented:
Still not attched
0
 
eossmaAuthor Commented:
attached.
Survey.xlsx
0
 
Katie PierceCommented:
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
 
eossmaAuthor Commented:
Thank you!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now