Concanate and Excel reading the data as Text?

This is a discussion on Concanate and Excel reading the data as Text? within the Excel Questions forums, part of the Question Forums category; 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 ...

Concanate and Excel reading the data as Text?

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.

Re: Concanate and Excel reading the data as Text?

I tried this and excel still cannot find the number using a VLOOKUP or this TEXT formula. Some numbers get picked up in the VLOOKUP, but others don't. CTRL F doesn't find the text in a VLOOKUP formuala but does in a regular text field.

Re: Concanate and Excel reading the data as Text?

I sure wish I could see your sheet to see what you are looking at, but here are a few tips that may help:

1. For a VLOOKUP to work, the two values that you are comparing MUST be the same data type (both Text or both Numeric). You can use the ISNUMBER function to verify if your entries are numeric or not.

2. Just because a value displays will leading zeroes when you look at it doesn't mean it really has leading zeroes in it. Custom Formats can be applied to have them displayed even though they really aren't part of the actual entry. The easiest way to tell is to select that cell and see what shows up in the Formula bar. Also note that anything entered as a number (and not text) will drop leading zeroes.