Split text by text

[raikks]

I would like to split the text below and the delimeter should be "-vs" & "h2h"?
Here is the link - beijing-guoan-fc-vs-shanghai-sipg-fc-h2h-stats#539665

Can anyone help?

 

[sandy666]

you mean like this?

Column1		Column1.1	Column1.2	Column1.3
beijing-guoan-fc-vs-shanghai-sipg-fc-h2h-stats#539665		beijing-guoan-fc	-shanghai-sipg-fc-	-stats#539665

 

[sandy666]

or like this

Column1		Column1
beijing-guoan-fc-vs-shanghai-sipg-fc-h2h-stats#539665		beijing-guoan-fc
		-shanghai-sipg-fc-
		-stats#539665

 

[raikks]

or like this

Column1		Column1
beijing-guoan-fc-vs-shanghai-sipg-fc-h2h-stats#539665		beijing-guoan-fc
		-shanghai-sipg-fc-
		-stats#539665

First option - sorry for the late reply.

 

[sandy666]

sure, you said First option but quoted the second
so here is Power Query M for the first (post#2):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitAnyC = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByAnyDelimiter({"-vs","h2h"}, QuoteStyle.Csv))
in
    SplitAnyC

btw. this is not a vba!

 

[raikks]

I think i clicked ``reply'' on your last message.
Where do i input that code (i'm not familiar with Power Query)?

 

[Peter_SSs]

You could also try the macro below (or do it manually in a few steps).
I have assumed data in column A, starting at row 2 with results to go beside in columns B:D (but any of that can be changed if required)

From looking at what that data most likely represents, you may actually want those red parts of the code to be "-vs-" and "-h2h-" so change that if required.

Sub Split_Text()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = .Offset(, -1).Value
    .Replace "-vs", ";", xlPart
    .Replace "h2h", ";", xlPart
    .TextToColumns , xlDelimited, , , False, True, False, False, False
    .Resize(, 3).Columns.AutoFit
  End With
End Sub

 

[raikks]

It works, thank you.
Cheers!

 

[Peter_SSs]

It works, thank you.
Cheers!

When more than one person is involved, it helps if you identify who you are addressing.

If it was my code then "you are welcome"

 

[raikks]

Yes, it was your code.
I think it works with Power Query too, but i'm not familiar with that and I didn't know where to input the code.
Again, thanks a lot Peter_SSs.
Also thank you sandy666 for the effort, i really appreciate it.