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.