@ 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?

 

[Michael M]

Read here.....

Ampersands Appearing in Formulas

I have recently been experiencing something I have not seen in my fifteen years of working with Excel: @ signs are appearing in my workbooks formulas directly after the = sign. For example...

www.mrexcel.com

ignore the mention of ampersands in the title

 

[niket]

Read here.....

Ampersands Appearing in Formulas

I have recently been experiencing something I have not seen in my fifteen years of working with Excel: @ signs are appearing in my workbooks formulas directly after the = sign. For example...

www.mrexcel.com

ignore the mention of ampersands in the title

But why is it impacting the formula? Or Can it be escaped?

 

[Michael M]

Did you read this part of the post I mentioned

Can you remove the @?​

Often you can. It depends on what the part of the formula to the right of the @ returns:

• If it returns a single value (the most common case), there will be no change by removing the @.
• If it returns a range or array, removing the @ will cause it to spill to the neighboring cells.

If you remove an automatically added @ and later open the workbook in an older version of Excel, it will appear as a legacy array formula (wrapped with braces {}), this is done to ensure the older version will not trigger implicit intersection.

 

[niket]

Did you read this part of the post I mentioned

Can you remove the @?​

Often you can. It depends on what the part of the formula to the right of the @ returns:

• If it returns a single value (the most common case), there will be no change by removing the @.
• If it returns a range or array, removing the @ will cause it to spill to the neighboring cells.

If you remove an automatically added @ and later open the workbook in an older version of Excel, it will appear as a legacy array formula (wrapped with braces {}), this is done to ensure the older version will not trigger implicit intersection.

I understood this but as it says "There will be no change by removing the @" but the formula I am using is returning 0 if

@cell

is in formula. If I remove @ then only it gives correct output.
Either I have to remove @ from formula (which is not possible through macros) or I have to prevent 365 it from adding. but both are not working.

 

[RoryA]

which is not possible through macros

What makes you say that?

Just out of interest, why are you using CELL without a cell reference?

 

[niket]

What makes you say that?

Just out of interest, why are you using CELL without a cell reference?

Sub ReplaceText()

Sheets("Admin").Select
 Range("A2:A3").Replace "@", "", xlPart

End Sub

I am new to Excel and VBA. I have used the above macro to replace the @ symbol but it doesn't replace. If I try to replace any word then it works fine.

I was doing this searchable drop list tutorial and due to some reasons it was not working as expected and from the comments I got this new formula and it worked in older versions of Excel 365. If I remove @ manually then only this formula works.

 

[Michael M]

I don't have Excel at the moment, but try

Sub MM1()
'code from Vitaya
Dim o$, n$
    o = "@"
    n = ""
    Range("A2") = Replace(Range("A2").Formula, o, n)
End Sub

 

[niket]

I don't have Excel at the moment, but try

Sub MM1()
'code from Vitaya
Dim o$, n$
    o = "@"
    n = ""
    Range("A2") = Replace(Range("A2").Formula, o, n)
End Sub

This is also not working. You can try it in any latest excel 365 version

by adding

=@CELL("address")

. Below Mini-sheet is also not showing '@'. Please see the attached images.

test.xlsm
	A
1	$A$2
2	$A$2
3	$A$2
4	$A$2
Sheet1

Cell Formulas
Range	Formula
A1:A4	A1	=CELL("address")

 

[Michael M]

I can't test anything because I don't have 365....and anything I copy to my version automatically removes the "@"