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