Microsoft Excel - How to distinguish a number to a formula in a cell

Row A cells contains either a number or a formula. I want to create the follwing formula for row B cells;
If cell A1 contains a number, then cell B1 will equal the value 5, and if cell A1 contains a formula then cell B1 will be the result of cell A1.
How can I create this IF formula in the cells of row B?

Hello Jean-Pierre
It cannot be done with just a formula but you can define a function that will allow you to do it using VBA.  I have not tested this method from another site.

Right click any sheet tab
Select: View code
Goto the menu Insert>Module
Paste the following code into the window that opens on the right

Function IsFormula(cell_ref As Range) 
    IsFormula = cell_ref.HasFormula 
End Function

Close that application to return to Excel

Now, try the function.

Enter a simple formula in cell A1: =SUM(Z1:Z10)

Enter this formula in B1: =IsFormula(A1)

You should get a result of TRUE.

You can then use this logical test in a standard IF function: =IF(IsFormula(A1),A1,5)

Regards
Harry

Thanks Harry, it worked perfectly

Harry,

I am using 2013 and it has the isformula already avail to me without the VBA.  but it is a cool solution anyway.  Don