Checking the value of the end of a string
 

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.

Checking the value of the end of a string
 

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.

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.

Checking the value of the end of a string
 

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.