Hi OzGrid,
I have list of emails in the same cell, each one inside brackets, separated with semicolons with a space as follows;
[[email protected]] ; [[email protected]] ; [[email protected]] ; [[email protected]] ; ... list goes on.
Here is my formula:
=TRIM(MID(LEFT(SUBSTITUTE(MID($A$19,FIND("|",SUBSTITUTE($A$19,";","|",3))+1,LEN($A$19)),";",REPT("",LEN($A$19))),LEN($A$19)),FIND("[",$A$19)+2,FIND("]",$A$19)-FIND("[",$A$19)-1))
This formula above, for instance, extracts email between 3rd and 4th semicolon, but I couldn't manage it works as expected; a left bracket remains or right one if I want to modify +1s in the formula.
Additionally, my main goal is to obtain all emails through a column from B1 to B50; how can I also manage semicolon number changed when I filled down (in the formula as shown above, substitute refers to 3rd semicolon, but I want it increased one by one so that all emails can be listed in order from B1 to B50.
Please note... I already figured out that I could have made some tricks like Find and Replace, Text to columns, or VBA but I do want to follow and complete this with the formula above with your advice.
Thank you much all in advance.
Onexc