Extract part of cell based on condition

[aziz5353az]

Hi excel community



I wonder if I can extract part of a cell based on a condition

for example:


lets say we have this table

I need a way to extract what in column 2 and distribute the results to the lasts columns


I hope the problem is clear .


thanks

 

[Eric W]

Re: how to extract part of cell based on condition ?

Welcome to the Board.

Try:

Excel 2012

	A	B	C	D	E	F
1	ID	links	AAA	AAA	CCC	CCC
2	4345	AAA-1,CCC-34	AAA-1		CCC-34
3	3453	CCC-21,AAA-34,AAA-45	AAA-34	AAA-45	CCC-21
4	563	CCC-55,CCC-212,AAA-3,AAA-98	AAA-3	AAA-98	CCC-55	CCC-212

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula C2 {=IFERROR(TRIM(LEFT(SUBSTITUTE(MID($B2,SMALL(IF(MID($B2,ROW(INDIRECT("1:"&LEN($B2)-LEN(C$1)+1)),LEN(C$1))=C$1,ROW(INDIRECT("1:"&LEN($B2)-LEN(C$1)+1))),COUNTIF($C$1:C$1,C$1)),LEN($B2)),",",REPT(" ",LEN($B2))),LEN($B2))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[bosco_yip]

Re: how to extract part of cell based on condition ?

or……….

In C2, copied across to F2 and all copied down 3 rows :

=IFERROR(C$1&LOOKUP(,-MID($B2,FIND(C$1,$B2,FIND(C$1,$B2)+COUNTIF($C$1:C$1,C$1)-1)+LEN(C$1)+1,ROW($1:$15))),"")

Regards
Bosco

 

[aziz5353az]

Re: how to extract part of cell based on condition ?

Thank you for the effort

but my actual dataset is not on the exact order or data sample I gave

I tried to implement the same with my data but I get blanks

 

[Eric W]

Re: how to extract part of cell based on condition ?

Which formula(s) did you try? Can you post a more realistic sample of your data? It's very hard to build a formula when we don't know what we're really working with.

 

[István Hirsch]

Re: how to extract part of cell based on condition ?

Another approach without CSE:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	ID	links	AAA1	AAA2	CCC1	CCC2	CCC3	CCC4	DDD1
2	4345	AAA-1,CCC-34,DDD-99,CCC-24	AAA-1		CCC-34	CCC-24			DDD-99
3	3453	CCC-21,AAA-34,AAA-45,CCC-2,CCC-45,CCC-12	AAA-34	AAA-45	CCC-21	CCC-2	CCC-45	CCC-12
4	563	CCC-55,CCC-212,AAA-3,AAA-98	AAA-3	AAA-98	CCC-55	CCC-212
Sheet1