+ Reply to Thread
Results 1 to 2 of 2

pull text string from cell containing random txt data?

  1. 10-30-2008, 07:02 PM #1
    effang
    effang is offline
    Registered User
    Join Date
    03-28-2007
    Posts
    8

    pull text string from cell containing random txt data?

    Hi,

    i have an excel sheet with the following text located in a single cell


    ===
    From: CashNetUSA <support@cashnetusa.com>
    To: sjegegagesmith73@yahoo.com
    Subject: This is a test subject
    Mime-Version: 1.0
    Content-Type: text/plain; charset=utf-8

    Joe Smith
    ===

    how would i create a formula to automatically pull all data in the "subject" line? In every instance, there would be the Subject: line, and then immeidately following, the Mime-Version: 1.0

    I would imagine some combination of find function, mid/left function, etc.

    Thanks!!
    Reply With QuoteRegister To Reply
  2. 10-30-2008, 07:40 PM #2
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    With that in A1,

    In B1: =FIND("Subject: ", A1)

    in C1: =FIND(CHAR(10), A1, B1)

    In D1: =MID(A1, B1 + LEN("Subject: "), C1 - B1 - LEN("Subject: "))

    Then you can put all those together in one big ugly formula:

    =MID(A1, FIND("Subject: ", A1) + LEN("Subject: "), FIND(CHAR(10), A1, FIND("Subject: ", A1)) - FIND("Subject: ", A1) - LEN("Subject: ") )
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1