Extract 4th email address from multi select cell with CHAR(10) as delimeter

PeggyLang

I have a multi select cell that contains 4 email address, separated by CHAR(10).

I have been able to isolate the 1st, isolate the 2nd and isolate the 3rd in columns to the right.

1st email is extracted with this formula —>
=IF(FIND(CHAR(10), [Names of Approvers needed]@row ) > 0, LEFT([Names of Approvers needed]@row , FIND(CHAR(10), [Names of Approvers needed]@row ) - 1), [Names of Approvers needed]@row )

2nd email is extracted with this formula —>
=IF(COUNTM([Names of Approvers needed]@row ) > 1, MID([Names of Approvers needed]@row , FIND(CHAR(10), [Names of Approvers needed]@row ) + 1, IF(COUNTM([Names of Approvers needed]@row ) > 2, FIND(CHAR(10), [Names of Approvers needed]@row , FIND(CHAR(10), [Names of Approvers needed]@row ) + 1) - FIND(CHAR(10), [Names of Approvers needed]@row ) + 1, LEN([Names of Approvers needed]@row ) - FIND(CHAR(10), [Names of Approvers needed]@row ))), "")

3rd email is extracted with this formula —>
=IF(COUNTM([Names of Approvers needed]@row ) > 2, MID([Names of Approvers needed]@row , FIND(CHAR(10), [Names of Approvers needed]@row , FIND(CHAR(10), [Names of Approvers needed]@row ) + 1) + 1, LEN([Names of Approvers needed]@row ) - FIND(CHAR(10), [Names of Approvers needed]@row , FIND(CHAR(10), [Names of Approvers needed]@row ) + 1)), "")

I'm going cross-eyed trying to put together the formula to extract the 4th email.

Help??

TVang

@PeggyLang, use SUBSTITUTE() to help. The function gives you the option to replace the nth instance of some text. In this case, when there are 4 email addresses, there will be 3 new line characters. The 4th email address is found following the 3rd new line character.

Location of 3rd new line character = FIND("*", SUBSTITUTE(approvers@row , CHAR(10), "*", 3))

IF(COUNTM(approvers@row ) = 4, MID(approvers@row , FIND("*", SUBSTITUTE(approvers@row , CHAR(10), "", 3))+1, 100), "") … assuming that there will be no email accounts longer than 100 characters.