Converting a column of text data by assigning numbers to each type of response

[westicles]

I have a long column of data where each cell in that column has 1 of 4 responses. I would like each of those responses to correspond to a number (0,1,2 or 3) and be converted either in an adjacent column of in the column itself so that every response has an assigned number.

[alansidman]

=if(A1="X",0,if(A1="Y",1,If(A1="Z",2,3)))

this should work. change the values to what is in your spreadsheet and copy down in the adjacent cell.

[oeldere]

or use Vlookup for that.

Make a table with the letters and the corresponding value.

Then you can use Vlookup to get the data.

the syntax off the formula will be:

Vlookup(a1,V1:W4,2,0)

[westicles]

Is there any reason why this wouldn't work?

=IF(AD3="1 to 3",0,IF(AD3="None",1,IF(AD3="More than 12",2,3)))

[oeldere]

Yes.

The syntax of the formula isn't right.

[alansidman]

On this site, look at the "If" functions, the "Or" function and the "And" function to understand the syntax.

http://www.techonthenet.com/excel/fo...x_ws_alpha.php

[westicles]

oeldere, can you explain why the syntax is wrong?

[alansidman]

Do your cells actually contain "1 to 3" or do they contain 1, 2 or 3. This is important to know in setting up the IF statement. From your original posting it appeared that the cells contained only 1 or four numbers. Please clarify or post a copy or your worksheet so that we can see what is really in your sheet.