+ Reply to Thread
Results 1 to 8 of 8

need to copy text that comes before one of two possible characters

  1. #1
    Registered User
    Join Date
    03-11-2013
    Location
    Vermont
    MS-Off Ver
    Excel 2011
    Posts
    6

    Exclamation need to copy text that comes before one of two possible characters

    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.

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: need to copy text that comes before one of two possible characters

    Try this:

    =LEFT(scheduleBEAR!E2,FIND("_",SUBSTITUTE(scheduleBEAR!E2," #","_"))-1)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    03-11-2013
    Location
    Vermont
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: need to copy text that comes before one of two possible characters

    Thanks, Tony. I'm still learning Excel. Been using it a few weeks now, and there's a LOT there. I haven't used SUBSTITUTE yet, I'll have to look that one up.

    That solution worked for combining my two formulas, but it doesn't recognize a title that has neither a # nor an _ in it. For instance, I just tried it with a title called Over The Hills, and it came up as #VALUE!. Somewhere in there I have to be able to account for shows that have no episode number or date.

    Thanks again, you got me over the first hurdle here.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: need to copy text that comes before one of two possible characters

    How about posting SEVERAL representative examples and show us what results you expect.

  5. #5
    Registered User
    Join Date
    03-11-2013
    Location
    Vermont
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: need to copy text that comes before one of two possible characters

    The Good News Broadcast_234_130311

    Abundant Living #34 (082112)

    Over The Hills

    Ready to Riot (Jarvis)

    Renewing Hope (120516) (Lamphere)

    _____________________________________

    I would like them to copy over as:


    The Good News Broadcast

    Abundant Living

    Over The Hills

    Ready to Riot

    Renewing Hope

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: need to copy text that comes before one of two possible characters

    OK, this works on all of those examples.

    =LEFT(scheduleBEAR!E2,FIND("_",SUBSTITUTE(SUBSTITUTE(scheduleBEAR!E2&"_"," #","_")," (","_"))-1)
    Last edited by Tony Valko; 03-11-2013 at 10:55 PM.

  7. #7
    Registered User
    Join Date
    03-11-2013
    Location
    Vermont
    MS-Off Ver
    Excel 2011
    Posts
    6

    [SOLVED] Re: need to copy text that comes before one of two possible characters

    That's fantastic. I've been pulling my hair out on this one.

    Anyway, I'll post this as solved. I am going to try to dissect this formula, because as of now I'm not quite sure I understand how it works. It works, yes. Perfectly. I just don't understand all of it. Thank you so much for helping me out on this.

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: [SOLVED] Re: need to copy text that comes before one of two possible characters

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1