Don't format the cells with the formula in it as Text - format the E1 cell as
Text so that it shows the leading zeros.
To fix the formulas you now have select them and change format to General
then hit [F2] followed by the [Enter] key - they'll convert to formula, one
by one. Just repeat [F2][Enter] as needed.

You can also fix your formula like this:
=CONCATENATE("[AMST.FIPS_STATE] LIKE ",CHAR(34),E1,CHAR(34))

the CHAR(34) is the double-quote mark. Easier to read also. Another
variation of the same formula without the CONCATENATE function:
="[AMST.FIPS_STATE] LIKE " & CHAR(34) & E1 & CHAR(34)
same result, and no difference with the number of things to concatenate that
you have at the moment, but this method does not have the 32 parameter limit
that the CONCATENATE() function does; instead the limit becomes the maximum
number of characters in a formula.


"datamia" wrote:

Problem #1: I am trying to concatenate using text in "" and cells. My cells
are formatted to text because I need to keep 0's in front of numbers in my
cells. The result is showing the formula and not the value. How can I get the
value? Ctrl+ ` doesn't work:

=CONCATENATE("[AMST.FIPS_STATE] LIKE "",E1,""")

Problem #2: You might have noticed lots of "s. In my concatenate result, I
need to have quotes, for example, the result should look like this:
[AMST.FIPS_STATE] LIKE "06". The quotes needed in my result are confusing the
formula.