Split text by text

raikks

New Member
Joined
Feb 23, 2020
Messages
29
Office Version
  1. 2019
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
you mean like this?
Column1Column1.1Column1.2Column1.3
beijing-guoan-fc-vs-shanghai-sipg-fc-h2h-stats#539665beijing-guoan-fc-shanghai-sipg-fc--stats#539665
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
or like this
Column1Column1
beijing-guoan-fc-vs-shanghai-sipg-fc-h2h-stats#539665beijing-guoan-fc
-shanghai-sipg-fc-
-stats#539665
 

raikks

New Member
Joined
Feb 23, 2020
Messages
29
Office Version
  1. 2019
Platform
  1. Windows
or like this
Column1Column1
beijing-guoan-fc-vs-shanghai-sipg-fc-h2h-stats#539665beijing-guoan-fc
-shanghai-sipg-fc-
-stats#539665
First option - sorry for the late reply.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

sure, you said First option but quoted the second
so here is Power Query M for the first (post#2):
Rich (BB code):
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!
 
Last edited:

raikks

New Member
Joined
Feb 23, 2020
Messages
29
Office Version
  1. 2019
Platform
  1. Windows
I think i clicked ``reply'' on your last message.
Where do i input that code (i'm not familiar with Power Query)?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

Rich (BB code):
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 23, 2020
Messages
29
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,110
Messages
5,599,773
Members
414,336
Latest member
Nicolas2465

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top