4

I'm using a formula that returns an Array of strings, so the result overwrites a range of cells, but I want to get the result in just that one cell.

(The actual formula I'm using is WIKISYNONYMS from a 3rd party add-on, but that shouldn't matter.)

I tried using ARRAY_CONSTRAIN, eg.

=ARRAY_CONSTRAIN( WIKISYNONYMS("en:Berlin"), 1, 1 )

But that throws away all the data that would have gone beyond the constraints, rather then constraining all the data to that one cell.

So I tried using concatenate, eg.

=CONCATENATE( WIKISYNONYMS("en:Berlin") )

And that is close, but I need to add ", " or something like that between each entry for readability.

1
  • @pnuts Yes, that does it. Make it an answer and I'll accept it.
    – Tom
    May 26, 2017 at 15:59

1 Answer 1

3

You can use JOIN to stitch the pieces together with a given delimiter:

=join(",", f o r m u l a ) 
1
  • 1
    Ok, and for the sake of completeness I'll note that becomes =join(", ", WIKISYNONYMS(A1)) with my formula if one is OK with trailing comma, otherwise =left(join(",", WIKISYNONYMS(H10) ),len(join(", ", WIKISYNONYMS(A1) ))-2).
    – Tom
    May 26, 2017 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.