### Tags: assign, calculate, excel, microsoft, msdn, number, sheet, software, trailers, values, vin, vins

# Assign values to letters

On Microsoft » Microsoft Excel

2,627 words with 2 Comments; publish: Thu, 22 May 2008 05:33:00 GMT; (30693.75, « »)

Hello I?m trying to set up an excel sheet to calculate VIN?s fo

trailers.

The VIN has 17 number/letters.

Number 9 is a ?check? number in the VIN, you get by taking al

numbers/letters and:

1) Take the assigned value, number or a letter converted to a number

2) Multiply by constant number

3) Add all there products

4) Divide by 11

5) Take the numerator of the reduced fraction and that is my 9t

number/letter

I would like to convert the letter to a number for a calculation tha

is required.

A=1

T=3

No real pattern, letters I,O,Q are not used

Is there a way assign a value to a letter then covert it to a number

if not already one?

And can I take just the numerator digit (reduced) from the cell, if i

has one?

Any help would be most appreciate

--

Message posted from http://www.ExcelForum.com

*http://excel.questionfor.info/q_microsoft-excel_24376.html*

All Comments

Leave a comment...

- 2 Comments
- Hi
to assign values to a letter you may use the following if there is no

pattern in your assigment:

=INDEX({1,3},MATCH(A1,{"A","T"},0))

Cell A1 stores the actaul letter for which a value should be returned.

Adapt the ranges to you needs

--

Regards

Frank Kabel

Frankfurt, Germany

> Hello I'm trying to set up an excel sheet to calculate VIN's for

> trailers.

> The VIN has 17 number/letters.

> Number 9 is a "check" number in the VIN, you get by taking all

> numbers/letters and:

>

> 1) Take the assigned value, number or a letter converted to a number

> 2) Multiply by constant number

> 3) Add all there products

> 4) Divide by 11

> 5) Take the numerator of the reduced fraction and that is my 9th

> number/letter

>

> I would like to convert the letter to a number for a calculation that

> is required.

> A=1

> T=3

> No real pattern, letters I,O,Q are not used

>

> Is there a way assign a value to a letter then covert it to a number,

> if not already one?

> And can I take just the numerator digit (reduced) from the cell, if

it

> has one?

> Any help would be most appreciated

>

> --

> Message posted from http://www.ExcelForum.com/

#1; Thu, 22 May 2008 05:35:00 GMT

- Hi
- Thank you Frank, that worked out great.
I matched numbers with numbers also

=INDEX({1,3,1,3),MATCH(A1,{"A","T",1,3},0))

so i could get a number back if one was entered.

Thanks again

--

Message posted from http://www.ExcelForum.com

#2; Thu, 22 May 2008 05:36:00 GMT

- Thank you Frank, that worked out great.