Is there a way in Excel to use SQL decode functionality?

[Ning]

Hi there,

I need to use SQL decode functionality in Excel. I tried but Excel won't accept decode. Is there a way to get the same effect in Excel?

Example: quite often, people use letters to represent numbers, like 1234D = 123.44, 5432G=543.27 in text files. To converter from letters to numbers, I can use decode (substr(A1,5,1), 'A', 1, 'B',2,'C',3,......,'{', 0, null).

Not sure how Excel handles this?

Thanks,
Ning

[mrice]

You could try the CHOOSE function

e.g. =CHOOSE(CODE(C3)-64,1,2,3)

converts A to 1, B to 2 and C to 3. The CODE for uppercase A is 65 hence the need to subtract 64 to create an index number.

[Ning]

Hi Martin,

Thanks for your suggestion. I don't think choose would work in my case. To use it, it needs to know/have a range of values, such as A, B, C.... In my case, we don't know what that single cell has and the only thing we know is that it could have A, B, C..... When it is A, return 1, when it is B, return 2 .... Choose is kind of like lookup function, I think. Anyway, I could be wrong. I would appreciate it if you can use the example I provide to show how it works.

Thanks,
Ning

[mrice]

How about

=CHOOSE(CODE(mid(C3,5,1))-64,1,2,3)

[Ning]

It works! Thank you very much for your help!