How to use braces in a formula? Syntactically, they seem to define collections inline in a formula.
How to get same results shown in https://apple.stackexchange.com/questions/356355/macos-numbers-and-tricks-with-braces
I think probably a more common term might be an "array" of values rather than a collection.
Here is a common use:
=XLOOKUP(A2,{1;2;3;4},{"one";"two";"three";"four"},"NF")
Or, if your region uses , as a decimal separator, then:
=XLOOKUP(A2;{1;2;3;4};{"one";"two";"three";"four"};"NF")
XLOOKUP looks at the value in cell A2, finds its position in the array of values between the first set of { } and then uses that position to look of the value in the second set of { }. This is more compact than putting the lookup values in ranges of cells.
SG
I think probably a more common term might be an "array" of values rather than a collection.
Here is a common use:
=XLOOKUP(A2,{1;2;3;4},{"one";"two";"three";"four"},"NF")
Or, if your region uses , as a decimal separator, then:
=XLOOKUP(A2;{1;2;3;4};{"one";"two";"three";"four"};"NF")
XLOOKUP looks at the value in cell A2, finds its position in the array of values between the first set of { } and then uses that position to look of the value in the second set of { }. This is more compact than putting the lookup values in ranges of cells.
SG
In Numbers, if you're trying to split by lines, it's even simpler that that:
=REGEX.EXTRACT($A2,".+",COLUMN()−1)
. means match any character, the + means one character or more.
Numbers automatically matches only one line at a time.
SG
Maybe this will work for you...
select cell B2, then type, or copy and paste from here the formula:
=REGEX.EXTRACT($A2,"(.+)\n(.+)\n(.+)",1, COLUMN()−1)
type the return key to enter the formula
select cell B2, copy
select cells B2 thru the end of column D (or as needed), paste
If you move to columns other than A with the source and B thru D then the formula will need to change to accommodate the column location. There are other ways to solve that, so post back if you have specific questions.
Wayne
In cell B10 try the following formula:
=IFERROR(REGEX.EXTRACT($A10,".+",COLUMN()−1),"")
Fill to the right to complete the row then fill down to complete the column
The " . " in the expression ".+" means "any character except new line". The "+" means keep extracting until you hit a new line character (or the end of the string).
Or you can hard code the numbers into the formula instead of using COLUMN
B10=IFERROR(REGEX.EXTRACT($A10,".+",1),"")
C10=IFERROR(REGEX.EXTRACT($A10,".+",2),"")
D10=IFERROR(REGEX.EXTRACT($A10,".+",3),"")
then fill down to complete the table
There are no cells "named Array" in a Numbers document.
There is a Numbers preference setting :
that permits using "names" in column and row "Header" cells in place of column letters and row numbers in formula cell references,
General preference stated by those answering questions in this community has been to NOT check this preference when taking screen shots to be placed in these discussions.
For trouble-shooting on a not familiar table, it is much quicker to locate cell K25 (which is always in the 25th row of the 11th column of the named table) than to find cell HaroldFortnight (which could be anywhere on the table, and require determining which column was labeled "Harold" and which row was labeled "Fortnight"
thanks. i need help on formula to accept array in another cell. The websites i found shows input being directly embedded in its formula but can input be indirect by referencing another cell?
Excel is much easier on this aspect but how does Numbers do this?
I am trying to group multiple inputs (indeterminate size) for a formula into single cell instead of a cell for every input; advantages include:
The following Excel example is very similar to what I am trying to do for Numbers;
I also wish there's something like Powershell split function
Split String using Regular Expression (Regex)
The following command splits the string which is in Distinguished Name format and returns only the name components.
1
2
$String = "OU=MTS,OU=CORP,DC=CONTOSO,DC=COM"
($String -split ',*..=' ) -ne ''
i finally found free Google App called Regex version 2.0.1 which educated me on Regex.
Following script is what i came up with to get text between curly braces delimited by commas; similar to Powershell split function of array format of indeterminate size { abc, def, etc }.
This one liner script frees me to
① have text o/s curly braces as comments.
② avoid needless steps for formatting multiple cells to accommodate each element of array
③ multiple cells of formulas
(?<=[{|,])\w+(?=[},])
Comments wellcome.
Hopefully Apple will add function called split()
thanks. i made progress but still need help on following
Sunnyintoronto123 wrote:
need help on following
The "following" is mysterious. What exactly are you trying to do? What problem are you trying to solve?
SG
I use Excel too. But I don't understand what you are asking. Could you give an example of what you are trying do, what problem you are trying to solve?
SG
Numbers using braces in a formula? Syntactically, they seem to define collections inline in a formula.