To remove leading zeros use value() function. Also lookup can be used with value function.

You cannot compare it with = sign.

You need to use:

For partial recognition:

=ISNUMBER(SEARCH(B1,A1))

For exact recognition:

=EXACT(C2,F2)

Make sure to trim cell before comparing if you are using EXACT function or otherwise you are going to get bad results.

1. Unmerge all cells in column A and B
2. Select all rows in column A and B from your first row of data to your last row of data.
3. Press CTRL+G to open the Go To box
4. Click Special
5. Choose Blank Cells
6. In the formula bar enter = then press the Up arrow. You’ll end up with a formula like =A3. DO NOT PRESS ENTER
7. Press CTRL+ENTER and this will populate your formula to all selected cells and simulate a “fill down”.
8. Copy-paste as values as required.

Taken from https://www.reddit.com/r/excel/comments/10w12bt/comment/j7kjeo7/?utm_source=share&utm_medium=web3x&utm_name=web3xcss&utm_term=1&utm_content=share_button