Why did Excel auto added the “@” sign?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
05-08-24 01:01 PM
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))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
09-08-24 10:18 AM - edited 09-08-24 10:19 AM
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:
- Select the cell where you want to enter the formula.
- 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))
- 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.