O365 excel formula question

HASSAN BIN NASIR DAR 306 Reputation points
2021-10-13T12:54:42.39+00:00

Hi,

I am using o365 excel.

In column A2 I have multiple email ids with different domain names. For example.

A2> test1@jaswant .com;test2@jaswant .com,test3@jaswant .com;ts@xyz .com,pk@123.com,hh@رشد .com

I want to separate all email address which are associate with abc.com and all abc.com email ids should be appear in B2 or B column.(It will be good if all abc.com email ids appears in Column B. each cell has one email id)

Regards

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,530 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,649 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viki Ji_MSFT 4,421 Reputation points
    2021-10-14T06:01:45.277+00:00

    Hi @HASSAN BIN NASIR DAR ,
    Welcome to Q&A forum!
    Per my testing, I think this might be a workaround:

    • Select the cell A2, and go to Data>Text to Columns>Delimited>Next>select Semicolon and Comma>click Finish.

    140367-image.png

    • And then copy the following array formula into the cell F2, press Ctrl + Shift + Enter.

    =INDEX($A$2:$E$2,SMALL(IF(RIGHT($A$2:$E$2,7)="abc.com",COLUMN($A$2:$E$2),4^8),ROW(A1)))&" "

    140397-image.png

    Hope the information is helpful to you.

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.