What statement to use?

[Paul]

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!

 

[lorneknight]

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
(e-mail address removed). thanks hopefully i could help you.

 

[JE McGimpsey]

One way:

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


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

 

[paul]

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

 

[paul]

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

 

[JE McGimpsey]

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.

 

[Tom Ogilvy]

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)