Split text

[thedeadzeds]

Hi Guys,

What would be the best way to split the below to show as per the result

text to split (all in one cell)
January (2024) Points: 67.00Rank: 18

result

cell 1	cell 2	cell 3	cell 4	cell 5
January (2024)	Points	67	Rank	18

 

[Cubist]

It doesn't look like you have a consistent delimiter. Can you state the rules for the splitting? Does this part have no space in between or is it a typo? "...67.00Rank..."

 

[Scott Huish]

If that's a typo and there's actually a space before Rank, then I would do this:

Book1
	A	B	C	D	E	F
1	January (2024) Points: 67.00 Rank: 18	January (2024)	Points	67.00	Rank	18
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXTBEFORE($A1," ",2)
C1:F1	C1	=SUBSTITUTE(INDEX(TEXTSPLIT($A1," "),COLUMN(C1)),":","")

 

[alansidman]

Assuming a missing Space after Rank, then alternative is with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByAnyDelimiter({" ",": "}, QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"Column1.1", "Column1.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
    #"Merged Columns"