How To Combine Cells In Excel For Mac

How can the answer be improved? In this Excel for Mac 2016 update to his popular series, author Dennis Taylor presents numerous formulas and functions in Excel and shows how to use them efficiently.

I present to you my ConcatenateRange VBA function (thanks Jean for the naming advice!). It will take a range of cells (any dimension, any direction, etc.) and merge them together into a single string. As an optional third parameter, you can add a seperator (like a space, or commas sererated).

In this case, you'd write this to use it: =ConcatenateRange(A1:A4) Function ConcatenateRange(ByVal cell_range As range, _ Optional ByVal separator As String) As String Dim newString As String Dim cell As Variant For Each cell in cell_range If Len(cell) 0 Then newString = newString & (separator & cell)) End if Next If Len(newString) 0 Then newString = Right$(newString, (Len(newString) - Len(separator))) End If ConcatenateRange = newString End Function. Use VBA's already existing Join function. VBA functions aren't exposed in Excel, so I wrap Join in a user-defined function that exposes its functionality. The simplest form is: Function JoinXL(arr As Variant, Optional delimiter As String = ' ') 'arr must be a one-dimensional array.

JoinXL = Join(arr, delimiter) End Function Example usage: =JoinXL(TRANSPOSE(A1:A4),' ') entered as an array formula (using Ctrl- Shift- Enter). Now, JoinXL accepts only one-dimensional arrays as input. In Excel, ranges return two-dimensional arrays. 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). For a horizontal range, you would have to do a double TRANSPOSE: =JoinXL(TRANSPOSE(TRANSPOSE(A1:D1))) 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. This usage of TRANSPOSE is a well-known way of converting 2D arrays into 1D arrays in Excel, but it looks terrible.

A more elegant solution would be to hide this away in the JoinXL VBA function. For Excel 2011 on Mac it's different. I did it as a three step process. • Create a column of values in column A. • In column B, to the right of the first cell, create a rule that uses the concatenate function on the column value and ','. For example, assuming A1 is the first row, the formula for B1 is =B1.

Office 365 for mac video. Office 365 customers get the new Office for Mac first. You’ll have Office applications on your Mac or PC, apps on tablets and smartphones for when you're on the go, and Office Online on the web for everywhere in between.

For the next row to row N, the formula is =Concatenate(',',A2). You end up with: QA,Sekuli,Testing,Applitools,Visual Testing,Test Automation,Selenium • In column C create a formula that concatenates all previous values. Because it is additive you will get all at the end. The formula for cell C1 is =B1. For all other rows to N, the formula is =Concatenate(C1,B2). And you get: QA,Sekuli QA,Sekuli,Testing QA,Sekuli,Testing,Applitools QA,Sekuli,Testing,Applitools,Visual Testing QA,Sekuli,Testing,Applitools,Visual Testing,Test Automation QA,Sekuli,Testing,Applitools,Visual Testing,Test Automation,Selenium The last cell of the list will be what you want.

This is compatible with Excel on Windows or Mac. I use the CONCATENATE method to take the values of a column and wrap quotes around them with columns in between in order to quickly populate the WHERE IN () clause of a SQL statement. I always just type =CONCATENATE('',B2,'',',') and then select that and drag it down, which creates =CONCATENATE('',B3,'',','), =CONCATENATE('',B4,'',','), etc.