Splitting concat into one column

[Mooncake1]

Hi everyone! I have a single cell with contains a concatenated bunch of values.

It looks like this

Orange-Blue-Green-Pink-Yellow-Green3-Cyan-Majenta-Brown-White-Aqua-Black

The concatenate is variable and changes a lot in size so it cant really be hardcoded
Im trying to make it trim these words out using "-" delimiter.

End goal is to make this split into one column range where it spills down one row for each word delimited.

EG:

Book1
	A	B	C	D	E	F	G	H
1	Orange-Blue-Green-Pink-Yellow-Green3-Cyan-Majenta-Brown-White-Aqua-Black
2
3	Result:	Orange
4		Blue
5		Green
6		Pink
7		Yellow
8		Green3
9		Cyan
10		Majenta
11		Brown
12		White
13		Aqua
14		Black
Sheet1

 

[Muhammad_Usman]

Hey Mooncake1 ! Welcome to Board.

You can use text to column option .

After select your data

You data look like this .

Then you can use Transpose function Like below pic

Book1.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Orange	Blue	Green	Pink	Yellow	Green3	Cyan	Majenta	Brown	White	Aqua	Black
3														Orange
4														Blue
5														Green
6														Pink
7														Yellow
8														Green3
9														Cyan
10														Majenta
11														Brown
12														White
13														Aqua
14														Black
15														#N/A
16														#N/A
17														#N/A
18														#N/A
19														#N/A
20
21
22
Sheet5

Cell Formulas
Range	Formula
O3:O19	O3	=TRANSPOSE(B2:M2)
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope it will resolve your porblem!

 

[jolivanes]

Sub Maybe()
Dim a
a = Split([A1], "-")
    Range("B1").Resize(UBound(a) + 1).Value = Application.Transpose(a)
End Sub

If you want it shorter.

Sub Maybe()
    Range("B1").Resize(UBound(Split([A1], "-")) + 1).Value = Application.Transpose(Split([A1], "-"))
End Sub

 

[YasserKhalil]

If you are using office 365, you can use FILTERXML

=FILTERXML("<AllText><Num>"&SUBSTITUTE(A1,"-","</Num><Num>")&"</Num></AllText>","//Num")

 

[Mooncake1]

If you are using office 365, you can use FILTERXML

=FILTERXML("<AllText><Num>"&SUBSTITUTE(A1,"-","</Num><Num>")&"</Num></AllText>","//Num")

cool thanks!
filterxml looks very useful!

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1")
in
    #"Split Column by Delimiter"

 

[Sektor]

If you are using office 365, you can use FILTERXML

According to FILTERXML documentation, it's available not only for Excel for Microsoft 365, but since Excel 2013.