Evaluating single datum arrays

Hi,

I have several array formulas that include non-array calculated arguments in some functions. This results in Excel treating these arguments as arrays of only one datum (e.g. '{2}'). These arguments are then evaluated as errors. I need a way of removing the braces, so that, for example, turning '{2}' to '2'. Any ideas? I realise it's probably dead simple.

Thanks.

Hi and welcome to the forum

Hard to say, without seeing a sample of what you are working with?

It's difficult for me to test entirely because I don't have MCONCAT function available........but, yes, your problem is caused by COLUMN function which returns an "array" like {2} rather than just 2 - sometimes that isn't a problem but in some functions or circumstances {2} can't be processed.

One option is to wrap every COLUMN function in another function like MAX or SUM - that will convert the array to a "scalar".....but I think you can use a simpler approach anyway, one that avoids COLUMN functions altogether - try this formula

=SUBSTITUTE(MCONCAT(IF(INDEX(Table1[[English]:[German]],MATCH(H2,Table1[Name:],0),0)>0,", "&Table1[[#Headers],[English]:[German]],"")),", ","",1)

confirmed with CTRL+SHIFT+ENTER

or possibly even simpler like this

=SUBSTITUTE(MCONCAT(IF(INDEX(Table1,MATCH(H2,Table1[Name:],0),0)=1,", "&Table1[#Headers],"")),", ","",1)

In that version checking for exactly 1 rather than >0

If you want to get rid of unnecessary commas you can do as I did in my suggested formulas

As part of the IF function you concatenate ", " to the front of each value, you don't use MCONCAT to include any separators......and then SUBSTITUTE removes the unnecessary first ", ".....like this.....

=SUBSTITUTE(MCONCAT(IF(OFFSET(Table1[[#Headers],[Name:]],MATCH($H$2,Table1[Name:]),SUM(COLUMN(Table1[[#Headers],[DoB:]])),1,SUM(COLUMN(Table1[[#Headers],[Stop]])-COLUMN(Table1[[#Headers],[DoB:]]))-1)>0,", "&OFFSET(Table1[[#Headers],[Name:]],0,SUM(COLUMN(Table1[[#Headers],[DoB:]])),1,SUM(COLUMN(Table1[[#Headers],[Stop]]))-SUM(COLUMN(Table1[[#Headers],[DoB:]]))-1),"")),", ","")

I still think the shortened version will work, where I used Table1[[English]:[German]] that will give you all columns between English and German - it certainly worked OK on the data you showed here........

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.