Hi I have a string in a column and the last two digits are going to decide
which value is going to appear in another cell.

EG -
YTR567TT
YTR567VT
YTR567CT

Because the first ends in T* I want to call this a 1, the second is V* and
this will be 2 and the last is C* and this should be 3.

How do I conditionally fill the cell with 1, 2 or 3?

I want to make it so it is carried on as a trend down the page too.

Thanks in advance.

Hi
use something like
=IF(MID(A1,LEN(A1)-1,1)="T",1,IF(MID(A1,LEN(A1)-1,1)="V",2,IF(MID(A1,LE
N(A1)-1,1)="C",3,"not defined")))

--
Regards
Frank Kabel
Frankfurt, Germany

"Rob H" schrieb im Newsbeitrag
...
Hi I have a string in a column and the last two digits are going to
decide
which value is going to appear in another cell.

EG -
YTR567TT
YTR567VT
YTR567CT

Because the first ends in T* I want to call this a 1, the second is
V* and
this will be 2 and the last is C* and this should be 3.

How do I conditionally fill the cell with 1, 2 or 3?

I want to make it so it is carried on as a trend down the page too.

Thanks in advance.

Rob,

If you'll actually have several of these, not just three, you can set up a
table:

TT 1
VT 2
CT 3
XT 4
etc.

Then use:

=VLOOKUP(RIGHT(A2,2), table, 2, FALSE)

Name the table, and use the name in the formula, or put in cell references
(and be sure to make them absolute):
=VLOOKUP(RIGHT(A2,2), $M1:$N4, 2, FALSE)

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Rob H" wrote in message
...
Hi I have a string in a column and the last two digits are going to decide
which value is going to appear in another cell.

EG -
YTR567TT
YTR567VT
YTR567CT

Because the first ends in T* I want to call this a 1, the second is V* and
this will be 2 and the last is C* and this should be 3.

How do I conditionally fill the cell with 1, 2 or 3?

I want to make it so it is carried on as a trend down the page too.

Thanks in advance.

Hi,

There are 104 Combinations of the suffix. The first letter of the suffix is
the deciding character for what goes into column 2. If I did create a table
and use Vlookup instead then could I create a table with

T* = 1
V* = 2
C* = 3
X* = 4

or would I have to list all 104?

Also from a performance point of view what are the benefits of having use
this code as apposed to the way Frank has recommended? I am pulling this
information as single rows from an ODBC source and each row is written once
at a time and then loops back for the next. Therefor it is possible to
include Franks code or yours.

Many Thanks.

Rob H

----- Original Message -----
From: "Earl Kiosterud"
Newsgroups: microsoft.public.excel.programming
Sent: Saturday, May 15, 2004 1:13 AM
Subject: Checking the value of the end of a string


Rob,

If you'll actually have several of these, not just three, you can set up a
table:

TT 1
VT 2
CT 3
XT 4
etc.

Then use:

=VLOOKUP(RIGHT(A2,2), table, 2, FALSE)

Name the table, and use the name in the formula, or put in cell references
(and be sure to make them absolute):
=VLOOKUP(RIGHT(A2,2), $M1:$N4, 2, FALSE)

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Rob H" wrote in message
...
Hi I have a string in a column and the last two digits are going to
decide
which value is going to appear in another cell.

EG -
YTR567TT
YTR567VT
YTR567CT

Because the first ends in T* I want to call this a 1, the second is V*
and
this will be 2 and the last is C* and this should be 3.

How do I conditionally fill the cell with 1, 2 or 3?

I want to make it so it is carried on as a trend down the page too.

Thanks in advance


"Earl Kiosterud" wrote in message
...
Rob,

If you'll actually have several of these, not just three, you can set up a
table:

TT 1
VT 2
CT 3
XT 4
etc.

Then use:

=VLOOKUP(RIGHT(A2,2), table, 2, FALSE)

Name the table, and use the name in the formula, or put in cell references
(and be sure to make them absolute):
=VLOOKUP(RIGHT(A2,2), $M1:$N4, 2, FALSE)

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Rob H" wrote in message
...
Hi I have a string in a column and the last two digits are going to
decide
which value is going to appear in another cell.

EG -
YTR567TT
YTR567VT
YTR567CT

Because the first ends in T* I want to call this a 1, the second is V*
and
this will be 2 and the last is C* and this should be 3.

How do I conditionally fill the cell with 1, 2 or 3?

I want to make it so it is carried on as a trend down the page too.

Thanks in advance.