+ Reply to Thread
Results 1 to 2 of 2

pull text string from cell containing random txt data?

  1. #1
    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 <[email protected]>
    To: [email protected]
    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!!

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    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 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