Extracting email addresses from certain cells and placing them all together in one cell.

[dpaton05]

I have information that I am going to copy into a spreadsheet from a webpage. It is a list of email addresses that I want to send emails to. I need it in the format of "xxx@xxx.com, xxx@xxx.com, xxx@xxx.com" for however many addresses are on the list. The addresses are all in column B. There is the person's name in B1, their address in B2 and some other data in B3. This process is then repeated for each email address with the name of the second person being in B4, their address in B5 and the additional data in B6 and so on for the entire list. I need to extract the emails just into a single cell in the format explained above. Could someone help me please?

 

[Herakles]

=INDEX(TEXTSPLIT($B$2,","),1)

This wIll extract the first email address from the delimited list where the list is in cell B2.

Instead of the number 1 use the row number.

An image of your data would help.

 

[dpaton05]

This is what my spreadsheet will look like. There are only 3 entries in here as it is just a sample but it will be in this format.

Book1
	A	B	C	D	E	F
1		Fred Brown	Main	September 3, 2022 3:34 pm
2		fredbrown@gmail.com
3		Edit | Delete
4		John Mert	Main	August 28, 2022 8:23 pm
5		johnmert@gmail.com
6		Edit | Delete
7		Max Hugo	Main	August 15, 2022 3:04 pm
8		maxhugo@gmail.com
9		Edit | Delete
10
11
12
13
Sheet1

The real spreadsheet has more people on it but for security reasons, I could not upload the actual names and emails so I have just made up those names and emails. For this data, I would want the result, in say, F1 to be "fredbrown@gmail.com, johnmert@gmail.com, maxhugo@gmail.com" for each email address in column B.

I think I explained that enough. If I didn't, let me know what other information you need.

Thanks

 

[Herakles]

This returns a delimited string containing the email addresses where B1:B13 is the range containing the data..

It assumes that the '@' character only exists in values that are emails.

=TEXTJOIN(",",TRUE,FILTER(B1:B13,ISNUMBER(SEARCH("@",B1:B13)),"No results"))

 

[dpaton05]

Thanks for that. I don't know how long the list will be so there could be any number of addresses in that format in column B, not just until row 13. It needs to search the column until a blank cell is found.

 

[Herakles]

Just change the 13 to the last row of data.

If there are 1000 rows then this will work:

=TEXTJOIN(",",TRUE,FILTER(B1:B1000,ISNUMBER(SEARCH("@",B1:B1000)),"No results"))

 

[dpaton05]

Isn't there a way to find the last row in the column each time the procedure is run as it may be constantly changing? I don't want to have to keep updating it when the number of records changes.

 

[Herakles]

=TEXTJOIN(",",TRUE,FILTER(B:B,ISNUMBER(SEARCH("@",B:B)),"No results"))

 

[dpaton05]

That almost got it thanks. It finished with xxx@ but missed the xxx.com. There was also another address after it that was missed. The last address that was copied was the second last address.

 

[dpaton05]

I also want to be able to select and copy the addresses as at the moment, they are visible but in the cell, there is only the formula that you provided.