Converting Letter to Numeric Value

[Guest]

I have imported a text file with study participant responses that were coded
A,B,C,D,E. In order to conduct a statistical analysis I need to convert
these to numeric values A=1, etc. I went to the CODE function but this codes
A=65, etc.and only codes one cell at a time. What function or formula is
necessary to code for my desired values and accomplished for the dataset.
Thanks.

 

[Gord Dibben]

Larry

In any cell enter =LOOKUP(B1,{"A","B","C","D","E"}, {1,2,3,4,5})

Adjust B1 to suit.

Can be copied down a column if the column contains letters.

Not case-sensitive.


Gord Dibben MS Excel MVP

 

[T. Valko]

If you have a range of cells that contain only one of these single letters:
A,B,C,D,E

Select the range of cells in question
Goto the menu Edit>Replace
Find what: A
Replace with: 1
Replace all

Repeat for the other letters.

 

[Peo Sjoblom]

Are they in the same cell or do you mean each cell has either A or B or C
etc?
If there are one letter per cell and you want to sum the cells it is easy

=SUMPRODUCT(COUNTIF(A1:A10,{"A";"B";"C";"D";"E"}),{1;2;3;4;5})

will sum A1:A10

 

[Pete_UK]

Just subtract 64 from the value that CODE gives you to yield 1-5 for A-
E. Copy the formula down.

Hope this helps.

Pete