Excel Formula - How to extract values between commas?

[YSWOO]

example

RANGE (A1) : AA, BB, CC, DD, EE, FF, GG, HH

TARGET
(B1) : AA
(B2) : BB
(B3) : CC
(B4) : DD
(B5) : EE
(B6) : FF
(B7) : GG
(B8) : HH

I made second part, but after second, i do not know how to make it...
=MID(A1,FIND(",",A1)+1,FIND(",",A1,FIND(",",A1)+1)-(FIND(",",A1)+1))

please help me to do..

 

[sandy666]

raw		raw
AA, BB, CC, DD, EE, FF, GG, HH		AA
		BB
		CC
		DD
		EE
		FF
		GG
		HH

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

 

[YSWOO]

Thanks for your answer!!!

BUT, can i get the answer just "formula" ?

i do not know how to use of power query...

 

[sandy666]

sure, maybe someone else will give you formula solution

anyway look at

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[Zot]

Try this to extract BB
=TRIM(MID(A1,FIND("#",SUBSTITUTE(A1,",","#",1))+2,2))

For CC, change the bold number to 2 and so on.

In SUBSTITUTE that bold number is instance number. The comma is replaced by # for Nth instance of comma and use FIND to find starting position for MID function. That's how it works.

 

[YasserKhalil]

Try this formula

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

 

[Dossfm0q]

VBA Option

Public Sub extract_values_between_commas()

Dim TxT As Variant
TxT = Split(Range("A1"), ",")

Range("B1:B" & UBound(TxT) + 1) = WorksheetFunction.Transpose(TxT)

End Sub

 

[Dossfm0q]

Formula option

Test.xlsx
	A	B
1	AA, BB, CC, DD, EE, FF, GG, HH	AA
2		BB
3		CC
4		DD
5		EE
6		FF
7		GG
8		HH
aa

Cell Formulas
Range	Formula
B1	B1	=IFERROR(LEFT(RIGHT(","&$A$1&",",LEN(","&$A$1&",")-FIND("|",SUBSTITUTE(","&$A$1&",",",","|",ROW()-ROW($B$1)+1))),FIND(",",RIGHT(","&$A$1&",",LEN(","&$A$1&",")-FIND("|",SUBSTITUTE(","&$A$1&",",",","|",ROW()-ROW($B$1)+1))))-1),"")

 

[Dossfm0q]

Feed back please

Correction

MID(A1,FIND(",",A1)+1,FIND(",",A1,FIND(",",A1)+1)-(FIND(",",A1)+1))

is

=IFERROR(TRIM(MID($A$1,FIND("|",SUBSTITUTE(","&$A$1&",",",","|",ROW()-ROW($B$1)+1)),FIND("|",SUBSTITUTE(" "&$A$1&",",",","|",ROW()-ROW($B$1)+1))-FIND("|",SUBSTITUTE(","&$A$1&",",",","|",ROW()-ROW($B$1)+1))-1)),"")

Test.xlsx
	A	B
1	AA, BB, CC, DD, EE, FF, GG, HH	AA
2		BB
3		CC
4		DD
5		EE
6		FF
7		GG
8		HH
aa

Cell Formulas
Range	Formula
B1:B8	B1	=IFERROR(TRIM(MID($A$1,FIND("|",SUBSTITUTE(","&$A$1&",",",","|",ROW()-ROW($B$1)+1)),FIND("|",SUBSTITUTE(" "&$A$1&",",",","|",ROW()-ROW($B$1)+1))-FIND("|",SUBSTITUTE(","&$A$1&",",",","|",ROW()-ROW($B$1)+1))-1)),"")

 

[YSWOO]

I took a my mistake!

AA, BB, CC... -> That is sample for looking convinient.

Actually, the Original Text is random. Just all text is located between commas.

It could be like AAA, BB, CCC,DDDDDD,EEEEEEEEEE,F.

Could you help me to figure it out?

Thanks