+ Reply to Thread
Results 1 to 8 of 8

How to collect Name and Company from Email Address?

  1. 01-26-2021, 09:46 AM #1
    AaruJaan
    AaruJaan is offline
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    How to collect Name and Company from Email Address?

    Hi Experts,
    I have a file (attached) with emails, I want to extract the Name and company name, any experts can help on this issue.

    Thanks in Advabce.

    Regards.
    Aaru
    Attached Images Attached Images
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-26-2021, 09:52 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,790

    Re: How to collect Name and Company from Email Address?

    So what should happen in rows 6, 7 and 8???

    Based ONLY on what you've shown so far:

    Excel 2016 (Windows) 32 bit
    C
    D
    E
    1
    		 First Name Last Name Company Name
    2
    		 =PROPER(LEFT(B2,FIND(".",B2)-1)) =TRIM(LEFT(SUBSTITUTE(PROPER(MID(B2,FIND(".",B2)+1,99)),"@",REPT(" ",99)),98)) =TRIM(LEFT(SUBSTITUTE(PROPER(MID(B2,FIND("@",B2)+1,99)),".",REPT(" ",99)),98))
    Sheet: Sheet1

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    1
    S#
    		 Email Address First Name Last Name Company Name
    2
    1
    		 [email protected] Michael Smith Ashurst
    3
    2
    		 [email protected] David Colven Roger-Bullivant
    4
    3
    		 [email protected] David Henderson Macroberts
    5
    4
    		 [email protected] Peter Allen Birkettlong
    Sheet: Sheet1
    Last edited by AliGW; 01-26-2021 at 09:58 AM.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 01-26-2021, 10:04 AM #3
    AaruJaan
    AaruJaan is offline
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: How to collect Name and Company from Email Address?

    Thanks for the help, but it is not working for me, please see the screenshot.
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  4. 01-26-2021, 10:06 AM #4
    AaruJaan
    AaruJaan is offline
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: How to collect Name and Company from Email Address?

    @ is coming in many rows
    Reply With QuoteRegister To Reply
  5. 01-26-2021, 10:10 AM #5
    Rick Rothstein
    Rick Rothstein is offline
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,410

    Re: How to collect Name and Company from Email Address?

    Try these formulas in the indicated cells...

    C2: =LEFT(B2,FIND("@",SUBSTITUTE(B2,".","@"))-1)

    D2: =MID(LEFT(B2,FIND("@",B2)-1),FIND(".",B2&".")+1,99)

    E2: =MID(LEFT(B2,FIND(".",B2,FIND("@",B2))-1),FIND("@",B2)+1,99)
    Reply With QuoteRegister To Reply
  6. 01-26-2021, 10:44 AM #6
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to collect Name and Company from Email Address?

    @Rick R

    Neat!

    All I'd add is wrapping them all in a PROPER() function.

    ...and somewhat surprisingly and coincidentally I used to be FD for a division of one of those companies in days gone by.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  7. 01-26-2021, 11:24 AM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,790

    Re: How to collect Name and Company from Email Address?

    ... and therefore, if these are REAL E-mail addresses, ... Oops!
    Reply With QuoteRegister To Reply
  8. 01-26-2021, 12:29 PM #8
    AaruJaan
    AaruJaan is offline
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: How to collect Name and Company from Email Address?

    Thanks, Rick, it is working.....you are great.
    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. Replace Word bookmark with email address with an new email address
    By VBA_Novice_VBA in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2020, 01:56 AM
  2. [SOLVED] vba email send from excel - ignore second email address if "ENTER EMAIL ADDRESS"
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-28-2016, 07:22 AM
  3. Pulling company name, street address, city, state, and zip from address
    By UNREAL2K4 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-13-2015, 12:53 PM
  4. Email selected print section to address located in a cell instead of email address in code
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-08-2012, 01:57 PM
  5. Removing prefix on email address to leave company name in field
    By ecommerabmercer in forum Excel General
    Replies: 4
    Last Post: 12-07-2009, 12:54 PM
  6. Search on company name to auto input company address
    By jamie.c in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-12-2009, 12:38 PM

Tags for this Thread

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