I used the split formula and took the "hyphen mark" - to split two team names as FC Tokyo-Cerezo Osaka (F1:G1 is correct). But some team names include - for example, Shimizu S-Pulse-Cerezo Osaka which will split over two cells, or sometimes there are two teams including - for example Paris Saint-Germain F.C.-Shimizu S-Pulse. I have used the REGEXREPLACE formula to try to replace the team name including -, however, the match schedule is random which team names are not fixed in the same place. Please someone help, thank you very much.

Example picture： https://imgur.com/VDyvsl6

CodePudding user response：

Try this formula in F1:

=ArrayFormula(IF(D:D="",,QUERY(IF(LEN(REGEXREPLACE(D:D,"[^-]",""))=1,SPLIT(D:D,"-"),IF(LEN(REGEXREPLACE(D:D,"[^-]",""))=2,IF(((NOT(ISERROR(VLOOKUP(REGEXEXTRACT(D:D,"^[^-] ")&"*",D:D,1,FALSE))))*(NOT(ISERROR(VLOOKUP(REGEXEXTRACT(D:D,"-(. )$")&"*",D:D,1,FALSE))))) ((NOT(ISERROR(VLOOKUP("*"&REGEXEXTRACT(D:D,"^[^-] "),D:D,1,FALSE))))*(NOT(ISERROR(VLOOKUP("*"&REGEXEXTRACT(D:D,"-(. )$"),D:D,1,FALSE))))),SPLIT(SUBSTITUTE(D:D,"-","|",1),"|"),SPLIT(SUBSTITUTE(D:D,"-","|",2),"|")),SPLIT(SUBSTITUTE(D:D,"-","|",2),"|"))),"Select Col1, Col2",0)))

I trust you'll understand why I can't explain here in full how the formula works. But basically, it treats three different cases differently:

1.) If there is only a single hyphen, SPLIT at that hyphen.

2.) If there are two hyphens, SPLIT at the first hyphen IF doing so would allow you to locate both elements of the SPLIT in their entirety either at the very beginning or the very end of any string anywhere within the original list; otherwise, SPLIT at the second hyphen.

3.) If there are three hyphens, locate and SPLIT at the center hyphen.

It may not behave flawlessly with a larger data set. But it holds up using your sample data set.