Pattern Matching in Excel 2007

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
0
manov77 (1)
1/25/2012 9:45:48 PM
excel 39879 articles. 2 followers. Follow

1 Replies
482 Views

Similar Articles

[PageSpeed] 28

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:D), it is found




-- 
isabelle



Le 2012-01-25 16:45, Manov Saypuri a �crit :
> 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
0
isabelle
1/25/2012 10:40:37 PM
Reply:

Similar Artilces: