Extract the cells that contains alphanumeric string from the column

[g3org]

Hello people.
It may not be possible but I am trying to extract all the cells that contain alphanumeric word.
I am looking to have all the words like A1TNGJVJN2849V or similar from column A in column B. How I am doing now, is not working well. Any Other sugestion will be great.

Many thanks.

Raportul zilnic.xlsx
	A	B	C	D	E
1	Laurentiu Cristian Vaduva	ID	Nume	Tura	Start Time
2		A1TNGJVJN2849V	Laurentiu Cristian Vaduva	CA_A114	Start Time 10:45 AM
3	CA_A114	AFHLQAVMEU03B	Gheorghe Vasia	CA_A116	Start Time 10:40 AM
4		A2YP2502JD31DS	Marius Mironeasa	CA_A117	Start Time 10:41 AM
5	A1TNGJVJN2849V
6
7	Start Time 10:45 AM
8
9	136/136 stops
10
11	Gheorghe Vasia
12
13	CA_A116
14
15	AFHLQAVMEU03B
16
17	Start Time 10:40 AM
18
19	137/137 stops
20
21	Marius Mironeasa
22
23	CA_A117
24
25	A2YP2502JD31DS
26
27	Start Time 10:41 AM
28
29	127/127 stops
Tabelle1

Cell Formulas
Range	Formula
C2:C22	C2	=IFERROR(OFFSET(INDEX(A:A,MATCH(B2,A:A,0)),-4,0), "" )
D2:D22	D2	=IFERROR(OFFSET(INDEX(A:A,MATCH(B2,A:A,0)),-2,0), "" )
E2:E22	E2	=IFERROR(OFFSET(INDEX(A:A,MATCH(B2,A:A,0)),2,0), "" )
B2	B2	=IFERROR(OFFSET(INDEX(A:A,MATCH(E1&"*",A:A,0)),-2,0), "" )
B3	B3	=IFERROR(OFFSET(INDEX(A9:A500,MATCH(E1&"*",A9:A500,0)),-2,0), "" )
B4	B4	=IFERROR(OFFSET(INDEX(A20:A501,MATCH(E1&"*",A20:A501,0)),-2,0), "" )
B5:B12	B5	=IFERROR(OFFSET(INDEX(A:A,MATCH(F5&"*",A:A,0)),-2,0), "" )

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Tabelle1!$A$1:$E$23	C11:E22, B11:B12, B7:E10, B5:B6, C2:E6, B2

 

[ExceLoki]

here's an idea, use filter for the tura values and then have the rest fill in from there
----------------------------

Book1
	A	B	C	D	E
1	Laurentiu Cristian Vaduva	ID	Nume	Tura	Start Time
2		A1TNGJVJN2849V	Laurentiu Cristian Vaduva	CA_A114	Start Time 10:45 AM
3	CA_A114	AFHLQAVMEU03B	Gheorghe Vasia	CA_A116	Start Time 10:40 AM
4		A2YP2502JD31DS	Marius Mironeasa	CA_A117	Start Time 10:41 AM
5	A1TNGJVJN2849V
6
7	Start Time 10:45 AM
8
9	136/136 stops
10
11	Gheorghe Vasia
12
13	CA_A116
14
15	AFHLQAVMEU03B
16
17	Start Time 10:40 AM
18
19	137/137 stops
20
21	Marius Mironeasa
22
23	CA_A117
24
25	A2YP2502JD31DS
26
27	Start Time 10:41 AM
28
29	127/127 stops
Sheet1

Cell Formulas
Range	Formula
B2:B12	B2	=IFERROR(OFFSET(INDEX(A:A,MATCH(D2,A:A,0)),2,0), "" )
C2:C22	C2	=IFERROR(OFFSET(INDEX(A:A,MATCH(B2,A:A,0)),-4,0), "" )
D2:D4	D2	=FILTER(A1:A29,(LEFT(A1:A29,3)="CA_"))
E2:E22	E2	=IFERROR(OFFSET(INDEX(A:A,MATCH(B2,A:A,0)),2,0), "" )
D13:D22	D13	=IFERROR(OFFSET(INDEX(A:A,MATCH(B13,A:A,0)),-2,0), "" )
Dynamic array formulas.

 

[g3org]

here's an idea, use filter for the tura values and then have the rest fill in from there
----------------------------

Book1
	A	B	C	D	E
1	Laurentiu Cristian Vaduva	ID	Nume	Tura	Start Time
2		A1TNGJVJN2849V	Laurentiu Cristian Vaduva	CA_A114	Start Time 10:45 AM
3	CA_A114	AFHLQAVMEU03B	Gheorghe Vasia	CA_A116	Start Time 10:40 AM
4		A2YP2502JD31DS	Marius Mironeasa	CA_A117	Start Time 10:41 AM
5	A1TNGJVJN2849V
6
7	Start Time 10:45 AM
8
9	136/136 stops
10
11	Gheorghe Vasia
12
13	CA_A116
14
15	AFHLQAVMEU03B
16
17	Start Time 10:40 AM
18
19	137/137 stops
20
21	Marius Mironeasa
22
23	CA_A117
24
25	A2YP2502JD31DS
26
27	Start Time 10:41 AM
28
29	127/127 stops
Sheet1

Cell Formulas
Range	Formula
B2:B12	B2	=IFERROR(OFFSET(INDEX(A:A,MATCH(D2,A:A,0)),2,0), "" )
C2:C22	C2	=IFERROR(OFFSET(INDEX(A:A,MATCH(B2,A:A,0)),-4,0), "" )
D2:D4	D2	=FILTER(A1:A29,(LEFT(A1:A29,3)="CA_"))
E2:E22	E2	=IFERROR(OFFSET(INDEX(A:A,MATCH(B2,A:A,0)),2,0), "" )
D13:D22	D13	=IFERROR(OFFSET(INDEX(A:A,MATCH(B13,A:A,0)),-2,0), "" )
Dynamic array formulas.

Thank you for your replay!
Works perfect your solution, however I didn't thought about that and so I missed to show that instate of "CA_" can also be "SC_" and "XA_". Can be possible to have all this as parameters?
Thank you!

 

[g3org]

I have solved it. Many thanks.

 

[Peter_SSs]

I have solved it. Many thanks.

Glad you have a solution. Does it still involve the volatile function OFFSET? If used a lot in a worksheet it can have a negative impact on the sheet's performance.

Could this single formula in a single cell save all those volatile functions (assuming that you have the relevant functions available)?

23 05 02.xlsm
	A	B	C	D	E
1	Laurentiu Cristian Vaduva	ID	Nume	Tura	Start Time
2		A1TNGJVJN2849V	Laurentiu Cristian Vaduva	CA_A114	Start Time 10:45 AM
3	CA_A114	AFHLQAVMEU03B	Gheorghe Vasia	CA_A116	Start Time 10:40 AM
4		A2YP2502JD31DS	Marius Mironeasa	CA_A117	Start Time 10:41 AM
5	A1TNGJVJN2849V
6
7	Start Time 10:45 AM
8
9	136/136 stops
10
11	Gheorghe Vasia
12
13	CA_A116
14
15	AFHLQAVMEU03B
16
17	Start Time 10:40 AM
18
19	137/137 stops
20
21	Marius Mironeasa
22
23	CA_A117
24
25	A2YP2502JD31DS
26
27	Start Time 10:41 AM
28
29	127/127 stops
Sheet2 (3)

Cell Formulas
Range	Formula
B2:E4	B2	=CHOOSECOLS(WRAPROWS(FILTER(A1:A29,A1:A29<>""),5),{3,1,2,4})
Dynamic array formulas.

If you do not yet have WRAPROWS or CHOOSECOLS then here is another possible alternative that also avoids OFFSET and is only required in the top result row as the other results all spill down.

23 05 02.xlsm
	A	B	C	D	E
1	Laurentiu Cristian Vaduva	ID	Nume	Tura	Start Time
2		A1TNGJVJN2849V	Laurentiu Cristian Vaduva	CA_A114	Start Time 10:45 AM
3	CA_A114	AFHLQAVMEU03B	Gheorghe Vasia	CA_A116	Start Time 10:40 AM
4		A2YP2502JD31DS	Marius Mironeasa	CA_A117	Start Time 10:41 AM
5	A1TNGJVJN2849V
6
7	Start Time 10:45 AM
8
9	136/136 stops
10
11	Gheorghe Vasia
12
13	CA_A116
14
15	AFHLQAVMEU03B
16
17	Start Time 10:40 AM
18
19	137/137 stops
20
21	Marius Mironeasa
22
23	CA_A117
24
25	A2YP2502JD31DS
26
27	Start Time 10:41 AM
28
29	127/127 stops
Extract (2)

Cell Formulas
Range	Formula
B2:B4	B2	=INDEX(A1:A29,MATCH(D2#,A1:A29,0)+2)
C2:C4	C2	=INDEX(A1:A29,MATCH(D2#,A1:A29,0)-2)
D2:D4	D2	=FILTER(A1:A29,ISNUMBER(FIND("_",A1:A29)))
E2:E4	E2	=INDEX(A1:A29,MATCH(D2#,A1:A29,0)+4)
Dynamic array formulas.

 

[g3org]

Hello Peter, thank you very for your respond. Your method also works great!

 

[Peter_SSs]

You're welcome. Thanks for the confirmation. The new dynamic array functions are very powerful!