A more elegant solution would be to hide this away in the JoinXL VBA function.įor Excel 2011 on Mac it's different. This usage of TRANSPOSE is a well-known way of converting 2D arrays into 1D arrays in Excel, but it looks terrible. The inner TRANSPOSE converts the 1×4 two-dimensional array into a 4×1 two-dimensional array, which the outer TRANSPOSE then converts into the expected 4-element one-dimensional array. In the above example, TRANSPOSE converts the 4×1 two-dimensional array into a 4-element one-dimensional array (this is the documented behaviour of TRANSPOSE when it is fed with a single-column two-dimensional array).įor a horizontal range, you would have to do a double TRANSPOSE: =JoinXL(TRANSPOSE(TRANSPOSE(A1:D1))) In Excel, ranges return two-dimensional arrays. Now, JoinXL accepts only one-dimensional arrays as input. The simplest form is: Function JoinXL(arr As Variant, Optional delimiter As String = ' ')Įxample usage: =JoinXL(TRANSPOSE(A1:A4),' ')Įntered as an array formula (using Ctrl- Shift- Enter). VBA functions aren't exposed in Excel, so I wrap Join in a user-defined function that exposes its functionality. Use VBA's already existing Join function.
