Let's say I have two cells - one contains "Boston Red Sox" and the
other contains "at Boston Red Sox". One denotes a home game and one
denotes an away game. I want a statement that will look at the cell
and if the first two letters are "at" I want to return the original
cell and if the "at" is not present, to return a value of "Rogers
Centre" even though it will always be the SkyDome in our hearts!

Using FIND, "at" returns a value of 1 which I can use in an IF
statement. The problem I have is that "at" is not present as the first
two letters it returns #VALUE! and if "Seattle Mariners" is in the
cell, the FIND statement returns a value of 3. I don't need to know
where the "at" is, I just want to know if it is present as the first
two characters in the cell! What is the best way to do this? With all
the time I have spent working on a solution, I could have done it
manually but I really would like to know!

Hi Paul, I am finding it hard to understand exactly what you mean,
would it be possible to send my the work your are talking about, the
reason why is i have something in the works with baseball roster that i
might be able to figure it out how i could help you out. my email is
. thanks hopefully i could help you.

One way:

=IF(LEFT(A1,3)="at ",A1,"Rogers Centre")


Note that I used 3 since Atlanta starts with "at".

In article ,
(Paul) wrote:

Let's say I have two cells - one contains "Boston Red Sox" and the
other contains "at Boston Red Sox". One denotes a home game and one
denotes an away game. I want a statement that will look at the cell
and if the first two letters are "at" I want to return the original
cell and if the "at" is not present, to return a value of "Rogers
Centre" even though it will always be the SkyDome in our hearts!

Using FIND, "at" returns a value of 1 which I can use in an IF
statement. The problem I have is that "at" is not present as the first
two letters it returns #VALUE! and if "Seattle Mariners" is in the
cell, the FIND statement returns a value of 3. I don't need to know
where the "at" is, I just want to know if it is present as the first
two characters in the cell! What is the best way to do this? With all
the time I have spent working on a solution, I could have done it
manually but I really would like to know!

This does not seem to work at it returns with "Rogers Centre" for all
examples.

Did you put the space in the test string

="at "

rather than

="at"

it worked fine for me if the entry started with At , AT , at , or aT (two
letters followed by a space as a minimum)

--
Regards,
Tom Ogilvy


"paul" wrote in message
oups.com...
This does not seem to work at it returns with "Rogers Centre" for all
examples.

Then your entries don't really start with the letters "at" followed by a
space.

If you pasted the data from the web, the web designer may have used a
non-breaking space (CHR(160)) instead of a space character (CHR(32)). In
that case, either substitute the characters (perhaps using

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


) or change the 3 to 2 and correct the Atlanta entry for interleague
play, if any.

In article .com,
"paul" wrote:

This does not seem to work at it returns with "Rogers Centre" for all
examples.

My bad! If I could type properly it would have worked! Thanks JE!