Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel show value not formula
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel show value not formula
On Fri, 19 Oct 2007 09:32:01 -0700, 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. You are missing a few quotes here... How about this: ="[AMST.FIPS_STATE] LIKE """ & TEXT(E1,"00") & """" Regards, B. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel show value not formula
Hello,
#1 you might have the column or cell formatted as text. Format as "General" and see if that works. #2 you have too many quotes, try this instead: =CONCATENATE("[AMST.FIPS_STATE] LIKE ",E1,"") HTH, JP On Oct 19, 12:32 pm, 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel show value not formula
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: want neg numbers to show red/pos green via if-then formula | Excel Worksheet Functions | |||
need Excel cell to show value, not formula | Excel Worksheet Functions | |||
Excel links to worksheets show only formula | Excel Discussion (Misc queries) | |||
Formula in Excel to show greater than 50, but less than 250? | Excel Worksheet Functions | |||
Why does Excel show a formula in ONE cell ? | Excel Discussion (Misc queries) |