Converting Letter to Numeric Value
 

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.
--
Larry

Converting Letter to Numeric Value
 

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

On Thu, 19 Jul 2007 15:38:00 -0700, Larry
wrote:

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.

Converting Letter to Numeric Value
 

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 EditReplace
Find what: A
Replace with: 1
Replace all

Repeat for the other letters.

--
Biff
Microsoft Excel MVP


"Larry" wrote in message
...
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.
--
Larry

Converting Letter to Numeric Value
 

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


--
Regards,

Peo Sjoblom


"Larry" wrote in message
...
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.
--
Larry

Converting Letter to Numeric Value
 

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

On Jul 19, 11:38 pm, Larry wrote:
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.
--
Larry