![how to consolidate data in excel using code how to consolidate data in excel using code](https://www.office-addins.com/_img/plugins-reviews/excel-merging-tables/016.png)
#HOW TO CONSOLIDATE DATA IN EXCEL USING CODE HOW TO#
Let's look at a fairly easy example that shows how to add a quotation mark to the resulting concatenated string using the & operator.īased on the Excel spreadsheet above, we can concatenate a quotation mark as follows: ="Apple " & """" & " Banana"
![how to consolidate data in excel using code how to consolidate data in excel using code](https://www.maketecheasier.com/assets/uploads/2018/02/Excel-Consolidate-7.jpg)
Since the & operator will concatenate string values that are enclosed in quotation marks, it isn't straight forward how to add a quotation mark character to the concatenated results. Here, we have concatenated the values from the two cells (A1 and A2), separated by a space character. Instead our result would appear as follows: " website" Once we click on Consolidate, we will get the Consolidate window as shown below. Then, go to the Data ribbon and click on Consolidate. We must first select cell B2 in the Consolidate Sheet.
![how to consolidate data in excel using code how to consolidate data in excel using code](https://www.spreadsheetweb.com/wp-content/uploads/2018/09/Connect-Your-Data-Sources-and-Automate-Updates-with-Excel-Data-Connection-9.png)
This will prevent our values from being squished together. We have created a template named Consolidated Sheet for this in a separate sheet. In this example, we have used the & operator to add a space character between the values in cell A1 and cell A2. This makes it very difficult to read the results.īased on the Excel spreadsheet above, we can concatenate a space character using the & operator as follows: =A1 & " " & A2 'disable these functions to enhance code. Dim MyFolder As String, MyFile As String, wbmain As Workbook, lastrow As Long. Sub openfiles () 'declare the variables used within the VBA code. Paste the code written below and save the file as a Macro enabled workbook (. Otherwise, you might get a long string with the concatenated values running together. Press the Alt+F11 key on Excel to open the VBA macro code editor. When you are concatenating values together, you might want to add space characters to separate your concatenated values. Result: "Alphabet soup" Concatenate Space Characters Let's look at some Excel & operator examples and explore hwo you would use the & operator as a worksheet function in Microsoft Excel:īased on the Excel spreadsheet above, the following & examples would return: =A1 & A2