Let the cell A1 has "abccba" Now I write a formula in the cell B1 =RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))) it gives value "a" or array string as{"a";"b";"c";"c";"b";"a"} Now when I concatenate this with =CONCATENATE(RIGHT(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))) it gives "a" but I expect "abccba" where am I getting wrong? how to concatenate array string? Thanks! regards,


Concatenate doesn't accept array values as argument; it only accepts discrete text values. When you input arrays, it just takes the first value as you have already found. If you want to pass a single argument as an array of text and have the result be a single string of all those arguments, you have to use VBA. Here's a UDF that would do that:

Public Function JoinText(arr()) As String
    For i = LBound(arr) To UBound(arr)
        JoinText = JoinText & arr(i, 1)
End Function

And here's the formula to put in a cell, which must be entered as an array formula with Ctrl+Shift+Enter:

| improve this answer | |
  • Hi! is there any direct formula? – Hemant Rupani Mar 27 '15 at 17:25
  • 1
    No. There is no built-in Excel function that can take a single argument with a variable number of items in an array and return a string that combines all of those elements. If the array is always of a fixed length, then you can make a very long formula to combine them. Can you try posting a question closer to your actual task at hand? The example you gave is a really complex way to get your original result. If you posted something with the exact details, we may be able to give you a better solution you haven't thought of. – Engineer Toast Mar 27 '15 at 17:37
  • 2
    Yes. Frankly shocking that Microsoft have not yet made this functionality a feature of the in-built worksheet functions. – XOR LX Mar 30 '15 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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