CURLY BRACKETS IN EXCEL

[saqibhanif37]

Hello. My name is Saqib.
I am confused with the array formulas and curly brackets used as functions but not array formulas. I do understand they look something like this
{=<formula line>}, I am wanting to learn, what does the curly bracket really do in excel if it is not an array formula? I mean there has been posts where people use functions, part of which is "...find({1,2,3,4,5,6,7,8,9,0},A2)..."
This function doesn't really appear to work if used like this:
Assuming Cell A2 is "Saqib6" and I use a function in B2 which is "find({1,2,3,4,5,6,7,8,9,0},A2), it wouldn't work. I'm only trying to understand the ligatures of the curly bracket.

 

[GraH - Guido]

Perhaps it makes more sense like in the attached workbook.
Some formulae can handle array input or can give an array result. With the new calculation engine (will be released in version 2019) you will see those values get spilled. In all previous version you don't see then, but all values are calculated.
If you want to learn, google Mike Girvin, the author of CONTROL+SHIFT +ENTER, a reference on array formulae, how they work, and how to build them.

 

[Peter Bartholomew]

It does work. The quantity in braces is an array constant, so
={1,2,3,4,5,6,7,8,9,0}
entered with CSE is a row array of 10 digits.
= FIND( {1,2,3,4,5,6,7,8,9,0}, "Saqib6" )
searches for each digit in turn within your name. Only 6 is present, so the function returns five #VALUE! errors, then a 6 showing the position of the digit 6 in the string, followed by four further #VALUE! errors. It might have been more instructive to search for {"a","b",6}, or at least more successful. That would have given
2,5,6
as an array of three results.

 

[saqibhanif37]

This. I don't get this. This is so annoying.

 

[Peter Bartholomew]

@saqib
That is because you have not selected the array of 10 cells and committed the formula with Ctrl+Shift+Enter.
The error you see is only the first result of 10. All but one will be errors because you seek to find a string that is not present.

 

[Peter Bartholomew]

Prettied up a little, so that it looks less like an error.
As Guido pointed out, on a dynamic array installation, it is no longer necessary to commit the formula to a contiguous range of cells with CSE.
All that is needed is to type the formula into one cell and enter. The array is a property of the one cell but it is displayed over multiple cells provided they are empty.
The attached file assumes a standard Office installation though.

 

[Khalid NGO]

Hi Saqib,

Visit the following almost 5 years old thread, you may find some interesting points:

Split Alphanumeric

Hi, I want to split contents of a cell in two different coloumns using excel only no VBA. e.g ali123 ---> ali(col1) and 123(col2) matt.ferrell786 ---> matt.ferrell(col1) and 786(col2) awesome998907 ---> awesome(col1) and 998907(col2) Contents of cell may be of any length. Regards Ali

chandoo.org

Regards,