@ is added in formulas for newer versions of excel 365.

[niket]

I am having below formula:
=IF(ISNUMBER(SEARCH(@CELL("contents"),B4)),MAX($A$1:A3)+1,0)

In older versions of excel before 1902 it is working as expected but in the later versions of excel 365 @ is getting added before CELL and formula is not working in it.
I have tried to replace '@' to '' using script but it is not getting replaced. Except @ all other characters were replacing through it.

Is there any way to remove it or can we prevent excel to add it in formula?

 

[niket]

I have found the fix for this. Below formula works for all excel 365 versions.

=IF(ISNUMBER(SEARCH(INDIRECT(CELL("address")),B4)),MAX($A$1:A3)+1,0)

 

[vhatp]

Hi, I also got troubled by this peculiarity while inserting formulas into cells using VBA. I use a dynamic-range function (e.g. FILTER) that spills into adjacent cells, but it got converted into the single-cell form.
I think I have found a nicer solution, avoiding the need for the nested INDIRECT function (which, AFAIK, significantly increases the permanent re-computing load of a sheet).

I found out that inserting the formula to the Range itself or to Range.Formula property results in adding the @ sign to the other similar properties such as Range.Formula2 (but not to the Range.Formula itself). As a result, the modified formula WITH the @ appears and evaluates within the cell.

The solution is therefore as simple as using the Range.Fomula2 property instead od Range.Fomula in VBA to insert a formula string into.

To fix already modified formulas and remove the @'s (instead of the non-functioning Replace method), you may use

Range("A1").Formula2 = Range("A1").Formula