Hi, I have a strange one.
Here is my formula
=IF(LEFT(B32,1)="6",CONCATENATE(L32,K32,B32),B32)
basically it looks at the first character in a cell, if it begins with 6, then Concatenate 2 zeros in front of it L and K, otherwise return the number in the Cell B32.
The reason I have to use 2 helper cells for L and K for Zeros, is because excel won't display 2 leading zeros when using them in a formula like in this example
=IF(LEFT(B32,1)="6","00"&B32),B32)
So now when I do a CTRL F and search for the text 13028196, excel won't find it
- Keep in mind this number is generated by the formula up top in the first example above. If I do a compare, excel see's it as TRUE.
So Excel says both numbers match. CTRL F doesn't find it. Therfore my VLOOKUP fails because excel can't find it!!!!!
Here is my formula
=IF(LEFT(B32,1)="6",CONCATENATE(L32,K32,B32),B32)
basically it looks at the first character in a cell, if it begins with 6, then Concatenate 2 zeros in front of it L and K, otherwise return the number in the Cell B32.
The reason I have to use 2 helper cells for L and K for Zeros, is because excel won't display 2 leading zeros when using them in a formula like in this example
=IF(LEFT(B32,1)="6","00"&B32),B32)
So now when I do a CTRL F and search for the text 13028196, excel won't find it
- Keep in mind this number is generated by the formula up top in the first example above. If I do a compare, excel see's it as TRUE.
So Excel says both numbers match. CTRL F doesn't find it. Therfore my VLOOKUP fails because excel can't find it!!!!!