Preceding zeros, search

[srizki]

Hello,

<TABLE style="WIDTH: 112pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=149 border=0 x:str><COLGROUP><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 4768" width=149><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 112pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left width=149 height=19>I have numbers like this in column A,

0WND60061F62
</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>0WS00WFPML08</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>0WS00WFPML08</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>0WS00WFPML08</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>0WS00WFPML08</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>0WS00WFPML08</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>0WS00WFPML08</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>T7OLIVERGBX</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>T7OLIVERGBX</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=left height=19>T7OLIVERGBX

I want to compare (match) it to column B, but column B, does not have the Zeros, before the numbers, I need a formula that ignore zero and match the number. I used conditional formatting using, =SUMPRODUCT(--ISNUMBER(SEARCH($B$1:$B:B$1000,A1))), But does not working correctly.
</TD></TR></TBODY></TABLE>

Thanks

 

[texasalynn]

try this as the formula in the conditional format

=ISNUMBER(SEARCH(B1,$A$1:$A$1000))

 

[srizki]

Thanks Alynn, but your formula gives the same result as mine.

 

[texasalynn]

Excel Workbook
	A	B	C
1	0WND60061F62	WND60061F62	TRUE
2	0WS00WFPML08	WS00WFPML08	TRUE
3	0WS00WFPML08	WS00WFPML07	FALSE
4	0WS00WFPML08	WS00WFPML09	FALSE
5	0WS00WFPML08	WS00WFPML08	TRUE
6	0WS00WFPML08	WS00WFPML08	TRUE
7	0WS00WFPML08	WS00WFPML08	TRUE
8	T7OLIVERGBX	T7OLIVERG1	FALSE
9	T7OLIVERGBX	T7OLIVERGBX	TRUE
10	T7OLIVERGBX	T7OLIVERGBX	TRUE
Sheet2

Excel 2003

Cell Formulas
Range	Formula
C1	=ISNUMBER(SEARCH(B1,$A$1:$A$1000))
C2	=ISNUMBER(SEARCH(B2,$A$1:$A$1000))
C3	=ISNUMBER(SEARCH(B3,$A$1:$A$1000))
C4	=ISNUMBER(SEARCH(B4,$A$1:$A$1000))
C5	=ISNUMBER(SEARCH(B5,$A$1:$A$1000))
C6	=ISNUMBER(SEARCH(B6,$A$1:$A$1000))
C7	=ISNUMBER(SEARCH(B7,$A$1:$A$1000))
C8	=ISNUMBER(SEARCH(B8,$A$1:$A$1000))
C9	=ISNUMBER(SEARCH(B9,$A$1:$A$1000))
C10	=ISNUMBER(SEARCH(B10,$A$1:$A$1000))

 

[srizki]

Thank you,