formula for 3rd character in name

[Hans]

This is probably relatively easy to do, but im too lazy
(its Monday!) to look it up in Excel help.

We are using Supplier codes that start with 2 numbers
followed by the name of the Supplier, eg 10MICROSOFT. I
want to make a formula that looks at the 3 character in
this code (the first letter) and then gives it a number,
depending on alpabethic groups. So Suppliers starting with
letters A-C should get number 1, those starting with
letters D-G should get number 2 etc.

Thanks in advance for your help.

Hans

 

[Andy B]

Hi

There are a few ways of doing this. This might help:

=CODE(MID(A1,3,1))-64

This will return the number in the alphabet of the letter in position 3. You
can use this to group your records, depending on how you split the letters
up.

Andy.

 

[Frank Kabel]

Hi Hans
just to give you and idea (as you didn't provide the logic of your
character numbering)
=MID(A1,3,1)
will give you the third charcater of cell A1

=CODE(MID(A1,3,1) )
wild give you the ASCII code of this character. This could be used for
calculation a number if you have a defined algorithmn for this
assigment (e.g. the thirs three chars will get a 1, the next three a 2
and so on)

If you have a lookup table which assigns each individual character to a
number (lets call this lookup_table) with column A the charatcer and
column B the assigned number you may use
=VLOOKUP(MID(A1,3,1) ,'lookup_table'!$A$1:$B$26,2,0)

 

[Ron Rosenfeld]

This is probably relatively easy to do, but im too lazy
(its Monday!) to look it up in Excel help.

We are using Supplier codes that start with 2 numbers
followed by the name of the Supplier, eg 10MICROSOFT. I
want to make a formula that looks at the 3 character in
this code (the first letter) and then gives it a number,
depending on alpabethic groups. So Suppliers starting with
letters A-C should get number 1, those starting with
letters D-G should get number 2 etc.

Thanks in advance for your help.

Hans

To extract the third character:

=MID(A1,3,1)

The two examples you give of converting this character to a number do not bring
to mind a mathematical formula, so I would use a lookup table.

You can combine the letters that give the same numeric code into the same cell
of the lookup table, and use a formula such as:



=VLOOKUP("*"&MID(A1,3,1)&"*",{"ABC",1;"DEFG",2;"HI",3;"JKLM",4;"NOPQR",5;"STUVW",6;"XYZ",7},2,0)

The array constant can be placed in a table for easier modification and
referred to by the table range.




--ron

 

[JulieD]

Hi Hans

you could try
=VLOOKUP(MID(A3,3,LEN(A3)-3),{"A",1;"D",2;"G",3;"J",4;"M",5;"P",6;"S",7;"V",
8;"Y",9},2,TRUE)

Cheers
JulieD

 

[Hans]

Thanks you all for your help. Its clear now.

Hans

-----Original Message-----
Hi Hans
just to give you and idea (as you didn't provide the logic of your
character numbering)
=MID(A1,3,1)
will give you the third charcater of cell A1

=CODE(MID(A1,3,1) )
wild give you the ASCII code of this character. This could be used for
calculation a number if you have a defined algorithmn for this
assigment (e.g. the thirs three chars will get a 1, the next three a 2
and so on)

If you have a lookup table which assigns each individual character to a
number (lets call this lookup_table) with column A the charatcer and
column B the assigned number you may use
=VLOOKUP(MID(A1,3,1) ,'lookup_table'!$A$1:$B$26,2,0)


--
Regards
Frank Kabel
Frankfurt, Germany


.