How do I identify if a cell in excel contains a formula or a numbe

I would like to identify some how if a cell contains just a written number 
(ex "25000") or contains a formula. 
I would like to do this because I have an array where some cell contains 
formulas but some are numbers and I would like to be able to use conditional 
formating to highlight the cells that just have a number.

Regards
/Tobias
0
Andersson (1)
9/27/2004 5:41:08 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
673 Views

Similar Articles

[PageSpeed] 6

Click on the cell and look at the formula bar. The formula or the value will
appear there.

Ian

"Tobias Andersson" <Tobias Andersson@discussions.microsoft.com> wrote in
message news:739C49F9-91D7-4F0C-8581-FF04AF3E9375@microsoft.com...
> I would like to identify some how if a cell contains just a written number
> (ex "25000") or contains a formula.
> I would like to do this because I have an array where some cell contains
> formulas but some are numbers and I would like to be able to use
conditional
> formating to highlight the cells that just have a number.
>
> Regards
> /Tobias


0
me1 (409)
9/27/2004 5:48:21 PM
Tobias,

Create a UDF like so

Function IsFormula(rng As Range)
    If rng.Count > 1 Then
        IsFormula = CVErr(xlErrRef)
    Else
        If rng.HasFormula Then
            IsFormula = True
        End If
    End If
End Function

Then select the cells, and got to Format>Conditional Formatting, select a
condition of Formula Is, add a formula of =IsFormula(A1) (assuming your
start cell is A1, and select a pattern to show.

-- 

HTH

RP

"Tobias Andersson" <Tobias Andersson@discussions.microsoft.com> wrote in
message news:739C49F9-91D7-4F0C-8581-FF04AF3E9375@microsoft.com...
> I would like to identify some how if a cell contains just a written number
> (ex "25000") or contains a formula.
> I would like to do this because I have an array where some cell contains
> formulas but some are numbers and I would like to be able to use
conditional
> formating to highlight the cells that just have a number.
>
> Regards
> /Tobias


0
bob.phillips1 (6510)
9/27/2004 6:04:09 PM
Tobias

Select your array.

Edit>Go To>Special>Constants. Uncheck all boxes except "number".

OK your way out.

With these selected cells give them a color.

Gord Dibben Excel MVP



On Mon, 27 Sep 2004 10:41:08 -0700, "Tobias Andersson" <Tobias
Andersson@discussions.microsoft.com> wrote:

>I would like to identify some how if a cell contains just a written number 
>(ex "25000") or contains a formula. 
>I would like to do this because I have an array where some cell contains 
>formulas but some are numbers and I would like to be able to use conditional 
>formating to highlight the cells that just have a number.
>
>Regards
>/Tobias

0
Gord
9/27/2004 6:10:36 PM
Reply:

Similar Artilces:

Map characters to their relative positions in a cell
I've been using an Excel 2003 macro to loop through text cells in a spreadsheet that is sent to me from another office. My macro loops until column H (formatted as text) is empty; that is ="" (equal to a null string). It's been worked just fine until recently when the macro seems to end prematurely. It returns a normal end but clearly there are additional rows yet to be processed. The row that causes the macro to finish prematurely always contains many clearly visible character comments, sometimes 5-6,000 characters in length. My hunch is that one or more...

Looking to automatically "unhide" rows in excel using hyperlinks
Using Excel 2000 and want to hide rows then have those rows automatically displayed if a hyperlink is selected. ...

IDD identifier range
Is there a predefined range of dialog resource identifiers or can one use any value from 0 to 65535? If a more specific range exists, is it a MFC implied restriction? Thanks! -- Alex >Is there a predefined range of dialog resource identifiers or can one >use any value from 0 to 65535? > >If a more specific range exists, is it a MFC implied restriction? It's an MFC restriction, see "TN020: ID Naming and Numbering Conventions" in your MSDN documentation. Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq >It's an MFC restriction, see "TN020: ID Naming ...

Sheet name from cell
Hi, Is it possible to assign the sheet a name which is entered in a cell. I know how to do this with VBA but would like to know if this is possibile with the help of formula. Row: A1 has name (Bill). I want that second sheet in the workbook should have the name entered in row A1. thanks, navin It can't be done without VBA. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "navin" <navin.narayana@gmail.com> wrote in message news:1170285069.554922.159530@l53g2000cwa.googlegroups.com....

How to copy a cell to another sheet having more than 255 characte.
It is copying only first 255 character.. How to resolve this problem to copy entire cell content ...

Why can't I do a "basic search" of my computer in Excel 2007
I can do a search of entire computer for info in Excel worksheets with older versions of Excel. In 2007 I can only search withing on sheet at a time. ...

Excel to Word question
I copied an Excel table into Word and then copied it to a CD-RW for someone else to update periodically. They can edit and make changes but are unable to Save the changes. Working with the CD, is there anything they can do to enable them to save? I no longer have the original Excel or Word tables on my computer. Never work on a file that's on removeable media. Never save directly to removeable media. Tell the user to copy the file to their hard drive, make their changes, then burn it back to the CD. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "...

Query Formula
Can you help with this formula to enter in MS query? Using ODBC t connect to data source but need query data to be restricted to dat range shown in two cells given on worksheet. Eg. A1 = 01/04/04 and A2 20/04/04 Query should only return records with dates 01-20/04/04 Many thank -- Message posted from http://www.ExcelForum.com ...

Cells to columns
Hi, Easy I know, but i just can't get. I have a list of names, A1:A100, and i want these to run across the top of my sheet as column titles. i.e. A1:CV1 can any one help. Thanks Craig Select A1:A100 Edit|Copy Select B1 (can't use A1) Edit|paste special|transpose Delete column A if you don't need it. craiglittleperth@aol.com wrote: > > Hi, > > Easy I know, but i just can't get. > > I have a list of names, A1:A100, and i want these to run across the > top of my sheet as column titles. i.e. A1:CV1 > > can any one help. > > Thanks > ...

I want to copy a cell (A) to another cell. But, only if I peg (A)
(A) is not a valid cell reference. What does "peg (A)" mean. Use the large piece of white space in the body part of your message to fill in some descriptions please. Gord Dibben MS Excel MVP On Thu, 1 May 2008 14:10:11 -0700, Rv <Rv@discussions.microsoft.com> wrote: ...

Excel 2000 Track Changes
Hello, I have just been asked by a user how to track changes in worksheet, which is fine. But they want the changes they make to b highlighted in a different font colour, rather like Word does. At th moment changes are highlighted by a border arround the changed cell. Is this standard in Excel, and if so is it difficult to change? Thank you for your attention, Tim -- Message posted from http://www.ExcelForum.com Hi AFAIK this cannot be changed to something similar to MS Word. -- Regards Frank Kabel Frankfurt, Germany "tim llewellin >" <<tim.llewellin.14capr@excelfor...

Exporting fields from access to excel
Hi, i know that it is possible to export data from Excal to Access and make sure the data falls into specific fields, but can it be done the other way round? I.e. can i have a preset spreadsheet with my fields designed, I click a button (either or excel or access) then those fields becoem populated with access info and there is no need for editing etc... Without programming in VBA, you have (at least) two manual options. First way: Create your Excel sheet so it looks like an Access table, with column headers that match the field names in Access. Save it and close it. In Access, go to...

How to count Number of cells holding a particular value.
HI All, In my table there are 3 columns. Each cell in the last column holds either 1 or 0. I want to get the sum of the cells of the last column on top of the column name. I used the sum function to do this and works fine. But my requirement is to get the sum of the cells in the last column when i Filter using 1st and 2nd columns. e.g. Filter using the first column would give 10 rows from 30 rows. I need the sum of the cells of the third row only for those 10 rows. How can I achieve this using functions? Thank You in advance. Gihan. Use SUBTOTAL(9, range) -- ____________________________...

What formula will take a name in one cell (last name,first name) .
I have a name in a cell, last name first then a comma then first name, and I want to seperate them into two cells. I know there is a formula to do that, but I don't know what it is, can anyone help me? Use the menu option Data:Test To Columns to parse the one field into two fields Good Luck Stewart "jobby" wrote: > I have a name in a cell, last name first then a comma then first name, and I > want to seperate them into two cells. I know there is a formula to do that, > but I don't know what it is, can anyone help me? You could use Data > Text to colum...

Calculation to replace the cell contents
Sorry to ask such basic questions but I only use Excel once a year for end of term marks. I have a column with results /82 eg; 56, 27, 49 (all out of 82) I want to convert the mark to /20 (out of 20) and for the results to replace the marks out of /82. So the formula should be mark/82 x 20 = mark out of 20 Could sm tell me apply this formula so that Excel will recalculate the column for me? Thanks in advance Hi Dave: You want to scale numbers in place: 1. in an un-used cell enter =20/82 2. copy this cell 3. select all the cells in the column you wish to re-scale 4. pull-down: ...

split color in cell...
A user just asked me if she could put a diagonal line in a cell, and then color the upper block one color, and the lower block another. The diagonal line is easy enough... format-cells-border and add the line. is it possible to split the background color in a cell??? (not that i've heard of, but sometimes things that I haven't heard of are possible) Hi AFAIK not possible interesting user request you get :-) -- Regards Frank Kabel Frankfurt, Germany mark wrote: > A user just asked me if she could put a diagonal line in a > cell, and then color the upper block one color,...

Pasting conditional cell formatting onto other cells
Suppose you have a matrix of cells, say in C11:D12 for simplicity, upon which you have imposed a conditional formatting scheme, so that, for example, cell C11 is shaded yellow and D12 is shaded cyan. Also suppose that the matrix in C11:D12 was derived from a matrix in A1:B2. Is there a simple way to impose the shading in the C11:D12 matrix onto the matrix in A1:B2, even though the conditional formatting imposed on C11:D12 is not applicable to A1:B2? Example: Here is C11:D12: 0.76 0.98 0.99 0.22 We impose on this matrix the conditional formatting scheme that all values between 0.50 an...

Format Cells?
Hello, Not sure if this is the easiest way to do this but I am wanting to create a series of cells A1 10.0.0 A2 10.0.1 A3 10.0.2 Etc. A14 10.0.15 A15 10.1.0 A16 10.1.1 A17 10.1.2 Etc. Is there an easy method of doing this perhaps with a Format function or will I have to create a sum to work out the last cell plus .0.1? Any help is appreciated. Andrew. Give this a try in any starting cell, eg in A1: =10&"."&INT((ROWS($1:1)-1)/16)&"."&MOD(ROWS($1:1)-1,16) Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "@Homeonthe...

Operations management in excel 2003
studying operations management strategy and analysis 6th edition ... lost my original cd ... in this book there are questions relating to OM Explorer ... few of my friends told me its a macro in excel 2000 havent seen it my self... can any one help me how to enable OM explorer in excel 2003. thanks to all for answering it :) ...

Excel and outlook #3
Hi there, got some VBA code somewher eon this site to send a spreadsheet as a attachment from excel, and I was wonderign if there is anyway to ge around outlook asking for permission to send the email by eithe writing code in vba or a setting in outlook thank -- Matt Housto ----------------------------------------------------------------------- Matt Houston's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=451 View this thread: http://www.excelforum.com/showthread.php?threadid=26678 Hi have you checked Ron's site: http://www.rondebruin.nl/sendmail.htm --...

Identifying the Active Fill Color
I have a routine that adds a shape to a selected cell and I would like to have the color of that shape be the last selected "fill" color (or the default fill color if no change made since Excel was started). How can I determine the active fill color from the "Fill Color" toolbar using VBA? Seems like there should be a way to get that color value. Thanks... Steve No one having answered this so far I thought I'd have a go.. I couldn't find anything in the object model to look at for this, but that's because I don't have an extensive excel object model to loo...

cells that have dates
I have a spreadsheet with dates in several columns I need to know if a date in column H is past the date in column D and if it is, format the date is column H to color RED Let's say you have a date in H1 and you want to check it against the date in D1. 1. Select H1 2. Go to Format Menu, Conditional Formatting 3. Select "Formula Is" and type =$H$1>$D$1 4. Hit "Format" button, Patterns tab and choose an appropriate color 5. Hit OK twice Now whenever you enter a date in H1 that is later than the date in D1, H1 will turn whatever color you specified. HTH, JP On No...

How do I use a "subtract" function in Excel?
Hi Kelly if you want to subtract the value in B1 from the value in A1 and have the answer display in C1 then in C1 type =A1-B1 Hope this helps Cheers JulieD "Kelly" <Kelly@discussions.microsoft.com> wrote in message news:5DF4C83F-6BF1-40A8-BC8A-692058A5C70B@microsoft.com... > ...

Testing cell value for greater than 0
Hi, The value in a cell is derived from a formula and can be one of the following: NA error Number Null I need TRUE to be returned when the cell value is greater than 0. This test fails as an error is returned when the cell value is an error How do I fix this? Thanks in advance for all the help. Regards, Raj "Raj" <rspai9@gmail.com> wrote: > I need TRUE to be returned when the cell value > is greater than 0. This test fails as an error > is returned when the cell value is an error How > do I fix this? =IF(ISNUMBER(A1), A1>0, FALSE) ...

Transfering information to the next free cell in a column
I'm trying to set up a worksheets to track the spending of two people by amount and category. Since it's the shared expenses of two people I'm trying to keep track of who each purchase was made by (so a person 1 total, person 2 total). QUESTION Is there a way for an expense that is in a certain category to be transfered to the next available cell in a column? BACKGROUND I set it up by making one sheet for input and one to display the information. The input having columns for person/category/amount and the display showing columns for categories and each person's total...