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..

 

[Zot]

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

I think the best approach is using VBA. Maybe convert code by Doccfm0q to become UDF

 

[Zot]

Since I'm not sure how you are going to present the output. I just created a UDF (User Defined Function)

Function GetData(rng As Range, Pos As Long, Optional Delimiter As String = ",") As String

Dim ArryStrS() As String

ArryStr = Split(rng, Delimiter)
GetData = Trim(ArryStr(Pos - 1))

End Function

Say your Data is in A1 = AAA, BB, CCC,DDDDDD,EEEEEEEEEE,F

If you want to get BB, then the formula you want to type in any cell is =GetData(A1,2) for data in position 2. If the delimiter is : (colon), just type =GetData(A1,2,":")

 

[YSWOO]

Thanks for your answer!!!

Awesome!!!

But, If I change "," comma to other like "@" ?

 

[Zot]

Thanks for your answer!!!

Awesome!!!

But, If I change "," comma to other like "@" ?

As stated, comma is default value. You can put @ in formula like sample provided before =GetData(A1,2,"@")

 

[YSWOO]

Try this formula

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

I want to use this formula

that is awesome!!

but the thing is that if i use not ",", but "@", How to change that?

 

[Dossfm0q]

Test.xlsx
	A	B
1	AA, BB, CC, DD, EE, FF, GG, HHAAA, BB, CCC,DDDDDD,EEEEEEEEEE,F.	AA
2		BB
3		CC
4		DD
5		EE
6		FF
7		GG
8		HHAAA
9		BB
10		CCC
11		DDDDDD
12		EEEEEEEEEE
13		F.
14
15
16
17
18
bb

Cell Formulas
Range	Formula
B1:B18	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)),"")

 

[jolivanes]

In case you want to try a little macro.

Sub Split_At_Comma()
    Cells(1, 2).Resize(UBound(Split(Cells(1, 1), ",")) + 1) = Application.Transpose(Split(Cells(1, 1), ","))
End Sub

 

[Peter_SSs]

What about this shorter one?

20 12 08.xlsm
	A	B
1	AAA, BB, CCC,DDDDDD,EEEEEEEEEE,F	AAA
2		BB
3		CCC
4		DDDDDD
5		EEEEEEEEEE
6		F
7
Split Cell

Cell Formulas
Range	Formula
B1:B7	B1	=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A$1,FIND("#",SUBSTITUTE(A$1&",",",","#",ROWS(B$1:B1)))-1),",",REPT(" ",99)),99)),"")

.. or with your question about using @ instead:

20 12 08.xlsm
	A	B
1	AAA@ BB@ CCC@DDDDDD@EEEEEEEEEE@F	AAA
2		BB
3		CCC
4		DDDDDD
5		EEEEEEEEEE
6		F
7
Split Cell (2)

Cell Formulas
Range	Formula
B1:B7	B1	=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A$1,FIND("#",SUBSTITUTE(A$1&"@","@","#",ROWS(B$1:B1)))-1),"@",REPT(" ",99)),99)),"")

 

[KP117]

Assume the data is in cell A1. Try this at B1.
=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",100)),1+100*(ROW($A$1:$A$50)-1),50))

 

[Peter_SSs]

Assume the data is in cell A1. Try this at B1.
=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",100)),1+100*(ROW($A$1:$A$50)-1),50))

Depending on just what the data can be like, that could easily fail.
Try it with this text in A1

AAAAAAAAAAA, BBBBBBBBBBB, CCCCCCCCCCC,DDDDDDDDDDD,EEEEEEEEEE,FFF,GGG