Computing Staff
  • 0

I Want Separate Email Ids In Excel Sheet

  • 0

<blockquote><div class=”vibrantContent”><center><div itemprop=”video” itemscope=”” itemtype=”http://schema.org/VideoObject”><iframe src=”/web/20201026153629if_/https://www.computing.net/img/screencast/play.html#382045-57″ height=”270″ width=”480″ marginwidth=”0″ marginheight=”0″ hspace=”0″ vspace=”0″ frameborder=”0″ scrolling=”no” allowfullscreen=”true” webkitallowfullscreen=”true” mozallowfullscreen=”true” style=”max-width: 95%; margin-top: 8px; border: 1px solid rgb(226, 226, 226); visibility: visible;” data-dashlane-frameid=”1062″></iframe><div style=”font-size: 11px; font-style: italic;”><meta itemprop=”contentURL” content=”http://bom-computing-screencasts.s3-website-us-east-1.amazonaws.com/screencasts/382045-57.mp4″><meta itemprop=”thumbnail” content=”/img/screencast_splash.jpg”><span itemprop=”name”>I want separate email ids in excel sheet like this</span><meta itemprop=”description” content=”I have email ids like this myidentifiers@example.com.au and I want to remove last two words au and want like this myidentifiers@example.com second one I want to separate email ids by name ( Not domains example * gmail.com) for example me@gmail….”></div></div></center><table width=”100%”><tbody><tr><td>I have email ids like this “myidentifiers@example.com.au” and I want to remove last two words “au” and want like this myidentifiers@example.com<p>second one I want to separate email ids by name ( Not domains example * gmail.com) for example<br>me@gmail.com<br>128@gmail.com<br>128@yahoo.com<br>1258@gmail.com<br>1258@yahoo.com<br>me@yahoo.com</p><p>I want only ” ME” email ids only <br>me@gmail.com<br>me@yahoo.com<br></p></td></tr></tbody></table></div></blockquote>

Share

1 Answer

  1. For your first requirement, there are a numerous solutions, depending on what the rest of your data looks like.
    Based strictly on your example, where there are 3 characters that you want to eliminate (.au) this will work. It simply returns three less characters than the original string, starting from the left:

    =LEFT(A1,LEN(A1)-3)

    If the part you want to strip off might contain something other than 3 characters, then we need to find the 2nd dot in the string and remove it and everything after it. This formula should work regardless of how many characters follow the 2nd dot.

    =LEFT(A1,FIND(“.”,A1,FIND(“.”,A1)+1)-1)

    For your second question, your text doesn’t match your screen capture, so I don’t really know what you are looking for.

    Your text says “I want only “ME” email ids only” but your screen capture shows support@168.com in the same column as the “me” emails.

    What exactly are you looking for?

    • 0