Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Paul
 
Posts: n/a
Default What statement to use?

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!
  #2   Report Post  
 
Posts: n/a
Default

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.

  #4   Report Post  
paul
 
Posts: n/a
Default

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

  #5   Report Post  
paul
 
Posts: n/a
Default

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



  #6   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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.

  #7   Report Post  
Tom Ogilvy
 
Posts: n/a
Default

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I fix a circular reference in a financial statement? drjayhawk25 Excel Discussion (Misc queries) 0 February 7th 05 06:19 PM
If statement and Isblank statement Rodney C. Excel Worksheet Functions 0 January 18th 05 09:39 PM
7+ nested if statement? Turi Excel Worksheet Functions 3 December 20th 04 08:55 PM
Statement lintan Excel Worksheet Functions 1 December 3rd 04 12:31 AM
IF Statement difficulty susan hayes Excel Worksheet Functions 3 November 2nd 04 10:46 PM


All times are GMT +1. The time now is 02:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"