+ Reply to Thread
Results 1 to 11 of 11

Extracting Text from long string

  1. 10-09-2013, 03:48 AM #1
    BigJonF
    BigJonF is offline
    Registered User
    Join Date
    10-09-2013
    Location
    Dorset, United Kingdom
    MS-Off Ver
    Office 365 on Windows 7 Pro
    Posts
    17

    Extracting Text from long string

    Hello,

    I exported a folder from outlook and just need the email addresses and the names of the recipients. Unfortunately, the only place that the names appear is at the start of the body of the email, for example, "Dear Mr Smith," or "Dear Mr. and Mrs. Jones,"

    I am therefore trying to extract the first few words of every cell in column B. Each entry shares one common character; the comma, and I thought I could use this, but I have been unable to get the formula right for this. I have tried:

    =LEFT(B1,(FIND(”,“,B1)-1))

    Any help would be greatly appreciated.

    Thanks,

    Jon
    Reply With QuoteRegister To Reply
  2. 10-09-2013, 03:52 AM #2
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Extracting Text from long string

    Hi,

    Could you give a few more examples of the strings you have and your expected results in each case?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  3. 10-09-2013, 03:52 AM #3
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,915

    Re: Extracting Text from long string

    post sample excel file
    Reply With QuoteRegister To Reply
  4. 10-09-2013, 03:58 AM #4
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Extracting Text from long string

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  5. 10-09-2013, 06:21 AM #5
    BigJonF
    BigJonF is offline
    Registered User
    Join Date
    10-09-2013
    Location
    Dorset, United Kingdom
    MS-Off Ver
    Office 365 on Windows 7 Pro
    Posts
    17

    Re: Extracting Text from long string

    Sorry, the original question wasn't that descriptive. I have attached a short version of the database. In column A is the body of the email sent to the hotel guest. What I want out of the body is the name and title so that it can be paired with the email address in column B and used to send out an email newsletter.

    So where it says "Dear Mr. Smith, I have pleasure in confirming your reservation..." I just want "Mr. Smith". In some cases (as in A3) the email is addressed to a couple, so it begins "Dear Mr. and Mrs. Smith," in which case I would just want "Mr. and Mrs. Smith". The one thing they all have in common is that they end with a comma.

    In some cases (as in A5), a proper greeting wasn't used in which case I do not have the name and won#t be able to get it (unless excel can do that too!). I just mention this because I don't know if it makes any difference to the formula?

    Many thanks,

    Jon
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 10-10-2013, 07:01 AM #6
    BigJonF
    BigJonF is offline
    Registered User
    Join Date
    10-09-2013
    Location
    Dorset, United Kingdom
    MS-Off Ver
    Office 365 on Windows 7 Pro
    Posts
    17

    Re: Extracting Text from long string

    Just bumping this thread!

    Thanks.
    Reply With QuoteRegister To Reply
  7. 10-10-2013, 07:21 AM #7
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Extracting Text from long string

    So apart from those where we haven't got a name (in which case no amount of magic will get us it!), can we assume that, as in your examples, the strings always begin with "Dear"? Or have you left out some potential variations from your attachment?

    Regards
    Reply With QuoteRegister To Reply
  8. 10-10-2013, 07:29 AM #8
    BigJonF
    BigJonF is offline
    Registered User
    Join Date
    10-09-2013
    Location
    Dorset, United Kingdom
    MS-Off Ver
    Office 365 on Windows 7 Pro
    Posts
    17

    Re: Extracting Text from long string

    Yes, with the exception of a few that start Hi (I can pull these out manually), all of the emails begin "Dear" and the names end with a comma.
    Thanks
    Reply With QuoteRegister To Reply
  9. 10-10-2013, 07:33 AM #9
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Extracting Text from long string

    Ok, so maybe just:

    =TRIM(MID(A2,FIND(" ",A2),FIND(",",A2)-FIND(" ",A2)))

    Regards
    Reply With QuoteRegister To Reply
  10. 10-10-2013, 07:55 AM #10
    BigJonF
    BigJonF is offline
    Registered User
    Join Date
    10-09-2013
    Location
    Dorset, United Kingdom
    MS-Off Ver
    Office 365 on Windows 7 Pro
    Posts
    17

    Re: Extracting Text from long string

    Wow, thanks so much. I thought the formula would be a little simpler than that (which is why I had a go in the first place), no wonder I couldn't get it working!

    Do I mark as solved or admin? If so how do I do it? - just worked it out, thank you.

    Regards
    Reply With QuoteRegister To Reply
  11. 10-10-2013, 08:22 AM #11
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Extracting Text from long string

    You're welcome.
    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)

Similar Threads

  1. [SOLVED] Problem Extracting State(Text) from a long list of addresses.
    By seaspi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-11-2013, 05:25 PM
  2. String function to get selective text from long string
    By MWHLFC in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-29-2011, 02:51 PM
  3. string function to get selective text from long string
    By MWHLFC in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-25-2011, 05:00 PM
  4. Combining selected text into a long string of text
    By bruce71101 in forum Excel General
    Replies: 1
    Last Post: 02-22-2011, 02:42 AM
  5. [SOLVED] long text string
    By jhahes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-23-2006, 07:00 PM

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