I have television episodes on one sheet (named: scheduleBEAR). I need to copy a portion of the title to a cell on another sheet. My problem is, the data entry is inconsistent, and I can't fix it right now. I have to work with what I have. I have entries that look like this:
The Good News Broadcast_234_130311
Abundant Living #34 (082112)
Both have titles, episode numbers, and dates. I need to copy just the title from sheet 1 (scheduleBEAR) to sheet 2.
I tried this, and it worked for one or the other. How can I combine these? (for purposes of this example, the original was in cell E2 of the scheduleBEAR sheet)
=LEFT(scheduleBEAR!E2,SEARCH("_",scheduleBEAR!E2)-1)
or
=LEFT(scheduleBEAR!E2,SEARCH("#",scheduleBEAR!E2)-2)
The schedule changes weekly, so I need each of 600 cells to contain a formula that will cover whichever type of entry is in the original cell. Caveat: I can't single out numbers, because some titles have numbers in them (911 Truth Hour), and I can't single out non-alphanumeric characters, because some shows use one (Post-mortem Espresso). I can single out # and _ , because no shows contain either of those. Also, some shows have no number or date, and I need them to copy over without errors.
Thank you in advance.
Bookmarks