In case you have a requirement on combining multiple columns to on column & you did not have a clue then this whole article is for you. In this article we are going to learn how to combine multiple columns to one column using vba code. We want to combine the first two columns, with the First Name (column B) first and then the Last Name (column A). So, our two arguments for the function will be B2 and A2. There are two ways you can enter the arguments. To concatenate two or more columns, you configure the Merge Cells' settings in a similar way, but choose Columns under 'What to merge': Join rows column-by-column To combine data in each individual row, column-by-column, you choose to merge Rows, select the delimiter you want (line break in this example), configure other settings the way you.
In Microsoft Excel, concatenation is when the contents of two or more cells in a worksheet are combined into a third, separate cell. This process is accomplished by using either the CONCATENATE function or the concatenation operator.
Instructions in this article apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Office 365, Excel for Mac, and Excel Online.
CONCATENATE Syntax and Arguments
A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.
The syntax for the CONCATENATE function is:
Text1 (required): The first item to be combined. Can be text such as words or numbers, blank spaces surrounded by quotation marks, or cell references to the location of data in a worksheet.
Text2,Text3, ... Text255 (optional up to 255 text entries): The other items to be combined. Can be added to the CONCATENATE function to a maximum of 8,192 characters including spaces. Each entry must be separated by a comma.
Neither method of concatenation adds a blank space between words, which is fine when joining two parts of a compound word (like Baseball) into one or combining two series of numbers. When joining first and last names or an address, include the space in the concatenation formula, as shown in rows four, five, and six of the example image.
Concatenate Number Data
Even though numbers can be concatenated, as seen in row seven of the example image, the result 123456 is not considered a number by the program but is seen as text data.
The result shown in cell C7 cannot be used as arguments for certain math functions such as SUM and AVERAGE. If this type of entry is included with a function's arguments, it is treated like other text data and ignored.
One indication is that the concatenated data in cell C7 is aligned to the left, which is the default alignment for text data. The same result occurs if the CONCATENATE function is used instead of the concatenate operator.
How to Use the Excel CONCATENATE Function
Although it is possible to enter the complete function manually, it may be easier to use the dialog box to enter a function's arguments. The dialog box takes care of entering brackets, commas and, in this example, the quotation marks surrounding the blank space.
Here's how to enter the function into cell C4 using the dialog box:
Select cell C4 to make it the active cell.
Go to the Formulas tab.
Select Text to open the function drop-down list.
Choose CONCATENATE. In Excel 2019 and Excel 2016, choose CONCAT.
The CONCATENATE function has been replaced with the CONCAT function. The CONCATENATE function is still available in Excel 2019 and Excel 2016 for backward compatibility, but may not be available in future versions of Excel.
In the Function Arguments dialog box, place the cursor in the Text1 text box.
Select cellA4 in the worksheet to enter that cell reference.
Place the cursor in the Text2 text box.
Press the Space Bar on the keyboard to add a space. Excel adds double quotation marks around the space.
Place the cursor in the Text3 text box.
Select cellB4 in the worksheet to enter that cell reference.
Select OK to complete the function.
The concatenated name Mary Jones appears in cell C4.
When you select cell C4, the complete function appears in the formula bar above the worksheet.
Display the Ampersand in Concatenated Text
There are times where the ampersand character is used in place of the word such as in company names as shown inrow six of the example image.
To display the ampersand as a text character rather than have it act as the concatenation operator, surround it with double quotation marks like other text characters, as shown in the formula in row 6.
In this example, spaces are placed on either side of the ampersand to separate that character from the words on either side. To achieve this result, enter space characters on either side of the ampersand inside the double quotation marks like this:
If a concatenation formula uses the ampersand as the concatenation operator, the space characters and the ampersand surrounded by double quotes must also be included to have it appear as text in the formula results.
For example, the formula in cell C6could be replaced with the above formula to achieve the same results.
Last updated: August 2018
Applicability: Excel 2019, 2016, 2013; Office 365 and standard; Windows 7, 10 and MAC OS X.
When analyzing significant amounts of raw data in Microsoft Excel, you might find yourself in need of dividing or splitting text between different column cells. In today’s post we’ll cover three useful techniques that you will be able to use:
- Text to Columns – Delimited
- Text to Columns with Fixed Width
- Un-merging cells
Read further for the procedure – which applies with minor variations to Microsoft Office versions starting 2010.
Dividing to columns using Text to Columns – Delimited:
This technique is specially useful when interacting with data which is delimited by commas (CSV files) , semi colons, tabs, spaces and so forth.
- Open your Excel and open the database in which the Columns has to be separated.
- If you have Data’s in the next cell, select the cell and right click on it.
How To Combine Two Columns Text In Excel
- Click on Insert and select Shift Cells Right.
Wrap Text In Excel
- Now, select the whole column which has to be separated.
- Click on Data in the ribbon and Select Text to Columns.
- Select Delimited and click Next.
- Click on any one of the option in the Delimiters and select Next.
- Say for Example I have selected Space for separation.
- In the Column Data format Select your option if needed. It will be General as Default.
- To select in which cell the separation has to be done, Click on Destination and select the cells.
- After you have done with the selection, click Finish.
Dividing to Columns using Fixed width
- Open your spreadsheet and select the column cell which has to be divided.
- Again make sure no data is available next to the dividing cell.
- In your Excel header menu, click on Data and select Text to Columns.
- Select Fixed width and Click on Next.
- For creating a break line, click on desired position
- For deleting, double click on the break line.
- For moving, click and drag the break line.
- In the Column Data format Select your option if needed. It will be General as Default.
- To select in which cell the separation has to be done, Click on Destination and select the cells.
- Click Finish after selecting your format.
Un-merging cells
- To split merged cells, click on Merge & center and select Unmerge cells.
Thanks for reading