Is it possible to extract multiple parts of text from a cell?

[fapb48]

Hi,

I have multiple rows with email addresses and their status of the email.

Example: sales@contoso.com##Receive, Deliver;hr@contoso1.com##Receive, Deliver;purchase@contoso2.com##Receive, Deliver

But this goes into a pivot table it looks very dodgy, specially with 3000 rows in the spreadsheet.

Therefore is there way to just extract the email addresses before '##'.

I know that text before function does a similar thing but it only extract the first email address.

Thank you

 

[JEC]

How about

Copy Sheet Named Range in Table Issue.xlsm
	A	B
1	sales@contoso.com##Receive, Deliver;hr@contoso1.com##Receive, Deliver;purchase@contoso2.com##Receive, Deliver	sales@contoso.com
2		hr@contoso1.com
3		purchase@contoso2.com
4
Copy this Sheet twice

Cell Formulas
Range	Formula
B1:B3	B1	=TAKE(TEXTSPLIT(A1,"##",";"),,1)
Dynamic array formulas.

 

[fapb48]

How about

Copy Sheet Named Range in Table Issue.xlsm
	A	B
1	sales@contoso.com##Receive, Deliver;hr@contoso1.com##Receive, Deliver;purchase@contoso2.com##Receive, Deliver	sales@contoso.com
2		hr@contoso1.com
3		purchase@contoso2.com
4
Copy this Sheet twice

Cell Formulas
Range	Formula
B1:B3	B1	=TAKE(TEXTSPLIT(A1,"##",";"),,1)
Dynamic array formulas.

Thank you very very much. Its 99% what i need . the only if really is that when multiple emails extracted it drops to the next rows.. is there any way to populate into a single row at all?

Thank you for your help

 

[JEC]

If I understand you correctly you could use a textjoin

=TEXTJOIN(", ",,TAKE(TEXTSPLIT(A1,"##",";"),,1))

 

[fapb48]

If I understand you correctly you could use a textjoin

=TEXTJOIN(", ",,TAKE(TEXTSPLIT(A1,"##",";"),,1))

Thats is absolutely perfect. Thank you very very much!!!

 

[JEC]

Great, cheers!

 

[Rick Rothstein]

If I understand you correctly you could use a textjoin

=TEXTJOIN(", ",,TAKE(TEXTSPLIT(A1,"##",";"),,1))

Since you are using comma/space as the delimiter, you could have also done it this way...

=ARRAYTOTEXT(TAKE(TEXTSPLIT(A1,"##",";"),,1))

 

[JEC]

In my region it is delimted with ";" if I use ARRAYTOTEXT

 

[Rick Rothstein]

In my region it is delimted with ";" if I use ARRAYTOTEXT

Interesting... I did not know ARRAYTOTEXT was locale aware. Thanks for the alerting me to that.