Pattern Matching in Excel 2007

[Manov Saypuri]

Hi All

In each row of sheet, I have text filled in Col A, B, C and D. I am
looking to write a formula to search for pattern which matches BIS-DI-
eBDR-0003-1.0.0 from A1 to D1 and paste the result in Column i.e, E1.

I have about 30,000 rows of similar data and looking for a pattern to
match BIS-DI-eBDR-0003-1.0.0

BIS- Non numeric (3 letters)
DI- Non numeric (2 letters)
eBDR- Non numeric (4 letters)
0003- Numeric (4 numbers)
1. Numeric ( 1 number)
0. Numeric ( 1 number)
0 Numeric ( 1 number)


Column A Column
B Column C Column D Column E
BIS-DI-eBDR-0003-1.0.0.DOC ghdrht
rgergerg hwestgw
rfgreag BIS-DI-eBDR-0003-1.1.0.DOC
rgergerg rtrwe
erggqrehg ergrg BIS-DI-eBDR-0017-1.0.0.DOC 54yy5.ouu
fdrty
BIS-DI-eBDR-0018-1.0.0.doc erqggg
rrg het5

 

[isabelle]

hi Manov Saypuri,

you can put in the following cells (E1:K1), the informations to seach

E1 F1 G1 H1 I1 J1 K1
BIS DI eBDR 0003 1. 0. 0

and copy the following formula in cell E3:Kx

=IF(NOT(ISERROR(FIND(E$1,$A2))),1,IF(NOT(ISERROR(FIND(E$1,$B2))),2,IF(NOT(ISERROR(FIND(E$1,$C2))),3,IF(NOT(ISERROR(FIND(E$1,$D2))),4,0))))

based on the results other than 0, you can see the value found and in which column (1 to 4 for A), it is found




--
isabelle



Le 2012-01-25 16:45, Manov Saypuri a écrit :