Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 12
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 168
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel: want neg numbers to show red/pos green via if-then formula Jim Excel Worksheet Functions 4 May 16th 23 03:43 AM
need Excel cell to show value, not formula jori Excel Worksheet Functions 4 April 18th 09 05:47 PM
Excel links to worksheets show only formula Ben Elliott Excel Discussion (Misc queries) 3 February 6th 07 10:36 AM
Formula in Excel to show greater than 50, but less than 250? Janice B. - Cleveland, Ohio Excel Worksheet Functions 3 July 10th 06 10:07 PM
Why does Excel show a formula in ONE cell ? edpaul Excel Discussion (Misc queries) 1 August 12th 05 07:29 PM


All times are GMT +1. The time now is 11:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"