Extract the cells that contains alphanumeric string from the column

g3org

Board Regular
Joined
Jul 25, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1Laurentiu Cristian VaduvaIDNumeTuraStart Time
2A1TNGJVJN2849VLaurentiu Cristian VaduvaCA_A114Start Time 10:45 AM
3CA_A114AFHLQAVMEU03BGheorghe VasiaCA_A116Start Time 10:40 AM
4A2YP2502JD31DSMarius MironeasaCA_A117Start Time 10:41 AM
5A1TNGJVJN2849V    
6    
7Start Time 10:45 AM    
8    
9136/136 stops    
10    
11Gheorghe Vasia    
12    
13CA_A116   
14   
15AFHLQAVMEU03B   
16   
17Start Time 10:40 AM   
18   
19137/137 stops   
20   
21Marius Mironeasa   
22   
23CA_A117
24
25A2YP2502JD31DS
26
27Start Time 10:41 AM
28
29127/127 stops
Tabelle1
Cell Formulas
RangeFormula
C2:C22C2=IFERROR(OFFSET(INDEX(A:A,MATCH(B2,A:A,0)),-4,0), "" )
D2:D22D2=IFERROR(OFFSET(INDEX(A:A,MATCH(B2,A:A,0)),-2,0), "" )
E2:E22E2=IFERROR(OFFSET(INDEX(A:A,MATCH(B2,A:A,0)),2,0), "" )
B2B2=IFERROR(OFFSET(INDEX(A:A,MATCH(E1&"*",A:A,0)),-2,0), "" )
B3B3=IFERROR(OFFSET(INDEX(A9:A500,MATCH(E1&"*",A9:A500,0)),-2,0), "" )
B4B4=IFERROR(OFFSET(INDEX(A20:A501,MATCH(E1&"*",A20:A501,0)),-2,0), "" )
B5:B12B5=IFERROR(OFFSET(INDEX(A:A,MATCH(F5&"*",A:A,0)),-2,0), "" )
Named Ranges
NameRefers ToCells
_FilterDatabase=Tabelle1!$A$1:$E$23C11:E22, B11:B12, B7:E10, B5:B6, C2:E6, B2
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
here's an idea, use filter for the tura values and then have the rest fill in from there
----------------------------
Book1
ABCDE
1Laurentiu Cristian VaduvaIDNumeTuraStart Time
2A1TNGJVJN2849VLaurentiu Cristian VaduvaCA_A114Start Time 10:45 AM
3CA_A114AFHLQAVMEU03BGheorghe VasiaCA_A116Start Time 10:40 AM
4A2YP2502JD31DSMarius MironeasaCA_A117Start Time 10:41 AM
5A1TNGJVJN2849V   
6   
7Start Time 10:45 AM   
8   
9136/136 stops   
10   
11Gheorghe Vasia   
12   
13CA_A116   
14   
15AFHLQAVMEU03B   
16   
17Start Time 10:40 AM   
18   
19137/137 stops   
20   
21Marius Mironeasa   
22   
23CA_A117
24
25A2YP2502JD31DS
26
27Start Time 10:41 AM
28
29127/127 stops
Sheet1
Cell Formulas
RangeFormula
B2:B12B2=IFERROR(OFFSET(INDEX(A:A,MATCH(D2,A:A,0)),2,0), "" )
C2:C22C2=IFERROR(OFFSET(INDEX(A:A,MATCH(B2,A:A,0)),-4,0), "" )
D2:D4D2=FILTER(A1:A29,(LEFT(A1:A29,3)="CA_"))
E2:E22E2=IFERROR(OFFSET(INDEX(A:A,MATCH(B2,A:A,0)),2,0), "" )
D13:D22D13=IFERROR(OFFSET(INDEX(A:A,MATCH(B13,A:A,0)),-2,0), "" )
Dynamic array formulas.
 
Upvote 0
Solution
here's an idea, use filter for the tura values and then have the rest fill in from there
----------------------------
Book1
ABCDE
1Laurentiu Cristian VaduvaIDNumeTuraStart Time
2A1TNGJVJN2849VLaurentiu Cristian VaduvaCA_A114Start Time 10:45 AM
3CA_A114AFHLQAVMEU03BGheorghe VasiaCA_A116Start Time 10:40 AM
4A2YP2502JD31DSMarius MironeasaCA_A117Start Time 10:41 AM
5A1TNGJVJN2849V   
6   
7Start Time 10:45 AM   
8   
9136/136 stops   
10   
11Gheorghe Vasia   
12   
13CA_A116   
14   
15AFHLQAVMEU03B   
16   
17Start Time 10:40 AM   
18   
19137/137 stops   
20   
21Marius Mironeasa   
22   
23CA_A117
24
25A2YP2502JD31DS
26
27Start Time 10:41 AM
28
29127/127 stops
Sheet1
Cell Formulas
RangeFormula
B2:B12B2=IFERROR(OFFSET(INDEX(A:A,MATCH(D2,A:A,0)),2,0), "" )
C2:C22C2=IFERROR(OFFSET(INDEX(A:A,MATCH(B2,A:A,0)),-4,0), "" )
D2:D4D2=FILTER(A1:A29,(LEFT(A1:A29,3)="CA_"))
E2:E22E2=IFERROR(OFFSET(INDEX(A:A,MATCH(B2,A:A,0)),2,0), "" )
D13:D22D13=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!
 
Upvote 0
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
ABCDE
1Laurentiu Cristian VaduvaIDNumeTuraStart Time
2A1TNGJVJN2849VLaurentiu Cristian VaduvaCA_A114Start Time 10:45 AM
3CA_A114AFHLQAVMEU03BGheorghe VasiaCA_A116Start Time 10:40 AM
4A2YP2502JD31DSMarius MironeasaCA_A117Start Time 10:41 AM
5A1TNGJVJN2849V
6
7Start Time 10:45 AM
8
9136/136 stops
10
11Gheorghe Vasia
12
13CA_A116
14
15AFHLQAVMEU03B
16
17Start Time 10:40 AM
18
19137/137 stops
20
21Marius Mironeasa
22
23CA_A117
24
25A2YP2502JD31DS
26
27Start Time 10:41 AM
28
29127/127 stops
Sheet2 (3)
Cell Formulas
RangeFormula
B2:E4B2=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
ABCDE
1Laurentiu Cristian VaduvaIDNumeTuraStart Time
2A1TNGJVJN2849VLaurentiu Cristian VaduvaCA_A114Start Time 10:45 AM
3CA_A114AFHLQAVMEU03BGheorghe VasiaCA_A116Start Time 10:40 AM
4A2YP2502JD31DSMarius MironeasaCA_A117Start Time 10:41 AM
5A1TNGJVJN2849V
6
7Start Time 10:45 AM
8
9136/136 stops
10
11Gheorghe Vasia
12
13CA_A116
14
15AFHLQAVMEU03B
16
17Start Time 10:40 AM
18
19137/137 stops
20
21Marius Mironeasa
22
23CA_A117
24
25A2YP2502JD31DS
26
27Start Time 10:41 AM
28
29127/127 stops
Extract (2)
Cell Formulas
RangeFormula
B2:B4B2=INDEX(A1:A29,MATCH(D2#,A1:A29,0)+2)
C2:C4C2=INDEX(A1:A29,MATCH(D2#,A1:A29,0)-2)
D2:D4D2=FILTER(A1:A29,ISNUMBER(FIND("_",A1:A29)))
E2:E4E2=INDEX(A1:A29,MATCH(D2#,A1:A29,0)+4)
Dynamic array formulas.
 
Upvote 0
Hello Peter, thank you very for your respond. Your method also works great!
 
Upvote 0
You're welcome. Thanks for the confirmation. The new dynamic array functions are very powerful! :)
 
Upvote 1

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top