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! |
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. |
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!
|
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. |
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. |
All times are GMT +1. The time now is 03:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com