Why did Excel auto added the “@” sign?

vchinthakindi — Mon, 05 Aug 2024 12:01:35 GMT

When I write below formula into excel its auto adding @ symbol. How to avoid this?

I tried using set cell value and also placed value in collection, and tried write collection to excel as well

Expected:

=IF(ISNUMBER(SEARCH($N$1,(INDEX('Void Checks'!L:L,MATCH('Printed Checks'!B2&'Printed Checks'!D2,'Void Checks'!A:A&'Void Checks'!B:B,0))))),$N$1,IF(ISNUMBER(SEARCH($O$1,(INDEX('Cashed Checks'!F:F,MATCH('Printed Checks'!B2&'Printed Checks'!D2,'Cashed Checks'!A:A&'Cashed Checks'!B:B,0))))),$O$1,$P$1))

Result: (with @ symbol)

=IF(ISNUMBER(SEARCH($N$1,(INDEX('Void Checks'!L:L,MATCH('Printed Checks'!B2&'Printed Checks'!D2,@'Void Checks'!A:A&'Void Checks'!B:B,0))))),$N$1,IF(ISNUMBER(SEARCH($O$1,(INDEX('Cashed Checks'!F:F,MATCH('Printed Checks'!B2&'Printed Checks'!D2,@'Cashed Checks'!A:A&'Cashed Checks'!B:B,0))))),$O$1,$P$1))

Re: Why did Excel auto added the “@” sign?

Parthiban_Viatris24 — Fri, 09 Aug 2024 09:19:35 GMT

The @ symbol is automatically added by Excel when working with structured references or arrays in certain versions of Excel, particularly when using dynamic arrays and tables. The @ symbol is used to reference a single value in an array or table. However, if you want to avoid the @ symbol being added to your formula, you can use the following approaches:

1. Using Array Formula (Legacy Array Formulas)

You can enter the formula as an array formula by pressing Ctrl + Shift + Enter instead of just Enter when you input the formula. Excel will treat the formula as an array formula, which should prevent the @ symbol from being added automatically.
How to do it:
1. Select the cell where you want to enter the formula.
2. Paste the formula:
  =IF(ISNUMBER(SEARCH($N$1,(INDEX('Void Checks'!L:L,MATCH('Printed Checks'!B2&'Printed Checks'!D2,'Void Checks'!A:A&'Void Checks'!B:B,0))))),$N$1,IF(ISNUMBER(SEARCH($O$1,(INDEX('Cashed Checks'!F:F,MATCH('Printed Checks'!B2&'Printed Checks'!D2,'Cashed Checks'!A:A&'Cashed Checks'!B:B,0))))),$O$1,$P$1))
3. Instead of pressing Enter, press Ctrl + Shift + Enter. This will enter the formula as an array formula, and Excel will surround the formula with curly braces {}.

2. Using Explicit Array Syntax

For versions of Excel that support dynamic arrays (Excel 365 or Excel 2019), you can explicitly tell Excel to treat the formula as an array without adding @ by using the array syntax.
Simply make sure the formula references are correct and that you are not referencing individual cells but entire ranges or columns.

3. Using INDIRECT Function (Alternative Approach)

As a workaround, you can use the INDIRECT function to avoid Excel interpreting part of the formula as a table reference.
Example:
=IF(ISNUMBER(SEARCH($N$1,(INDEX('Void Checks'!L:L,MATCH('Printed Checks'!B2&'Printed Checks'!D2,INDIRECT("'Void Checks'!A:A")&INDIRECT("'Void Checks'!B:B"),0))))),$N$1,IF(ISNUMBER(SEARCH($O$1,(INDEX('Cashed Checks'!F:F,MATCH('Printed Checks'!B2&'Printed Checks'!D2,INDIRECT("'Cashed Checks'!A:A")&INDIRECT("'Cashed Checks'!B:B"),0))))),$O$1,$P$1))
The INDIRECT function converts the reference to a text string, which helps avoid the automatic @ symbol insertion.

By following these methods, you should be able to prevent Excel from automatically adding the @ symbol to your formula. If one approach doesn't work, try another, depending on your specific version of Excel and its behavior.

topic Re: Why did Excel auto added the “@” sign? in Product Forum

Why did Excel auto added the “@” sign?

Re: Why did Excel auto added the “@” sign?

1. Using Array Formula (Legacy Array Formulas)

2. Using Explicit Array Syntax

3. Using INDIRECT Function (Alternative Approach)