Spreadsheet clean text and split by regex

Question

I have this string:

Engaged View Beacon Url: https://ad.doubleclick.net/ddm/trackimp/..., Dcm Beacon Url: https://ad.doubleclick.net/ddm/trackimp/.....

I try to extract the two urls from it.

I have tried this, but I get only the first url. Why? How can I improve this?

=SPLIT(REGEXREPLACE(REGEXREPLACE(F21, "Dcm Beacon Url: ", "♥"), "Engaged View Beacon Url: ", ""), "♥")

Answer 1

try:

=ARRAYFORMULA(IFERROR(SPLIT(FLATTEN(
 QUERY(TRANSPOSE(IF(ISURL(SPLIT(A2, " ")), 
 SPLIT(A2, " "), )),,9^9)), " ")))

Answer 2

You haven't shown the entire string, so we don't know what markers there are to work with. That said, you can try this (assuming your raw string were in A2):

=REGEXEXTRACT(A2,REPT(".+(http[^\s,]+)",2))

The output will require two horizontal cells. So if you place this in B2, make sure that both B2 and C2 are free first.

If that doesn't work, consider sharing the entire string you are trying to break.

Answer 3

A simpler answer and modification I saw from here

=transpose(split(join(",",REGEXEXTRACT(A1,REGEXREPLACE(A1,"(https[^,]+)","($1)"))), ","))

You can opt to remove transpose, I just used it to show the result clearly.