+ Reply to Thread
Results 1 to 5 of 5

Trying to convert text into email with the help of neighbor cell

  1. #1
    Registered User
    Join Date
    05-25-2022
    Location
    Pakistan
    MS-Off Ver
    2019
    Posts
    3

    Trying to convert text into email with the help of neighbor cell

    Hi everybody, plz help me

    i have cells containing email addresses without "at" sign. now I have the website address or domain in the left neighbor cell. I want to use that cell's info to put "at" sign in the right place in the right cell.

    example

    prioritycargo.com.au nicholasprioritycargo.com.au
    publiship.com.au greigrpubliship.com.au
    fehlberg.com.au sdfehlbergfehlberg.com.au

    also please note that sometimes the value in the left call might not contain the text containing domain. In that case, I want Excel to ignore that.
    I hope I have explained it well.

    So please advise me a formula or way to do that on entire columns otherwise it will take forever to put manually "at" sign in thousands of cells

  2. #2
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Trying to convert text into email with the help of neighbor cell

    could you please upload your sample file that include your sample data and sample result ?

    in case domain is in A column and emai+domain (without @) in B column
    you may try this formula in C column.

    C1
    =IF(A1<>"",LEFT(B1,FIND(A1,B1))&"@"&A1,"")

    Regards.
    Last edited by menem; 05-25-2022 at 10:56 PM.

  3. #3
    Registered User
    Join Date
    05-25-2022
    Location
    Pakistan
    MS-Off Ver
    2019
    Posts
    3

    Re: Trying to convert text into email with the help of neighbor cell

    Thanks a lot menem for you reply.
    Your formula is working great except one error.
    In the result, email portion before "at" it is picking up one extra letter from the domain. the domain part on right of "at" sign is complete n ok.

    prioritycargo.com.au nicholasprioritycargo.com.au nicholasp"at"prioritycargo.com.au
    publiship.com.au greigrpubliship.com.au greigrp"at"publiship.com.au
    fehlberg.com.au sdfehlbergfehlberg.com.au sdfehlbergf"at"fehlberg.com.au

    Pz check what is causing error

    Thnx n Regards

  4. #4
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Trying to convert text into email with the help of neighbor cell

    Sorry for my mistake.

    =IF(A1<>"",LEFT(B1,FIND(A1,B1)-1)&"@"&A1,"")

    Regards.

  5. #5
    Registered User
    Join Date
    05-25-2022
    Location
    Pakistan
    MS-Off Ver
    2019
    Posts
    3

    Re: Trying to convert text into email with the help of neighbor cell

    Thanks menem. Now its working like a charm.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find neighbor cell 5x5
    By bennyistanto in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-12-2020, 05:08 AM
  2. Track Values in a Cell with a Neighbor Cell Containing Specific Keywords, how?
    By Rapmastac2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-08-2019, 02:09 PM
  3. dynamic suggestion with auto populate neighbor cell
    By 123789456 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-28-2017, 09:58 AM
  4. VBA excel to email convert to text
    By boon-yao.tek in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-10-2014, 05:33 AM
  5. Replies: 1
    Last Post: 09-28-2011, 03:57 PM
  6. Disabling a cell if it's neighbor has a certain value
    By ysbn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-18-2007, 03:42 AM
  7. Copy cells to neighbor column after leaving the cell
    By nemadrias in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-12-2006, 08:25 AM

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