How to Concatenate Two Columns in Excel with Hyphen: 9 Quick Methods

Method 1 – Using the CONCATENATE Function to Concatenate Two Columns with a Hyphen

Steps:
➤ Use the following formula in cell D4.

B4 is the Product Apple, C4 is the State of Delivery Delivered, and “-” is the separator between them.

➤ Press ENTER and drag down the Fill Handle tool.

Result:
We concatenated the products in the Product column and the delivery states in the State of Delivery column with a hyphen as a separator.

Method 2 – Using CONCATENATE Function and CHAR Function

The products with corresponding delivery conditions with a separator hyphen use the CHAR function with the CONCATENATE function.

Steps:
➤ Use the following formula in cell D4

B4 is the Product Apple, C4 is the State of Delivery Delivered and CHAR(45) is for the hyphen.

➤ Press ENTER and drag down the Fill Handle tool.

Result:
You can concatenate the products of the Product column and the delivery states of the State of Delivery column with a hyphen as a separator.

Method 3 – Using Ampersand Operator to Concatenate Two Columns in Excel with Hyphen

Steps:
➤ Type the following formula in cell D4.

B4 is the Product Apple, C4 is the State of Delivery Delivered, and “-” is the separator between them.

➤ Press ENTER and drag down the Fill Handle tool.

Result:
Get the concatenation of the products in the Product column, and the delivery states in the State of Delivery column with a hyphen as a separator.

Method 4 – Using TEXTJOIN Function to Concatenate Two Columns with Hyphen

Steps:
➤ Type the following formula in cell D4

B4 is the Product Apple, C4 is the State of Delivery Delivered, “-” is the separator between them and TRUE is for ignoring empty cells.

➤ Press ENTER and drag down the Fill Handle tool.

Result:
Combine the products in the Product column and the delivery states in the State of Delivery column with a hyphen as a separator.

Method 5 – Using Flash Fill Feature to Concatenate Two Columns with Hyphen

The Flash Fill feature of Excel is also helpful in combining the values of two columns.

Steps:
➤ Write the combination in the product Apple and the delivery state Delivered with a hyphen between them like below

➤Start typing the combination for the second row like the previous one.
The suggestions for the remaining rows will appear with the help of the Flash Fill feature, as seen in the following figure.
➤ Press ENTER.

Result:
You will have the combination of the products in the Product column and the delivery states in the State of Delivery column with a hyphen as a separator.

Method 6 – Combining Text String and Numeric Values with Hyphen from Two Columns

Steps:
➤ Use the following formula in cell D4

B4 is the Product Apple, C4 is the Sales value of $1,402.00, CHAR(45) is for hyphen and TEXT will maintain the Accounting format of the sales values.

➤ Press ENTER and drag down the Fill Handle tool.

Result:
You can unite the products in the Product column and the sales values in the Sales column with a hyphen as a separator.

Method 7 – Combining Text String and Date Values with Hyphen from Two Columns

Steps:
➤ Type the following formula in cell D4

B4 is the Product Apple, C4 is the Delivery Date of 1/9/2021, “-” is the separator between them and TEXT will maintain the Date format in m/d/yyyy of the delivery dates.

➤ Press ENTER and drag down the Fill Handle tool.

Result:
Get the concatenation of the products in the Product column and the dates in the Delivery Date column with a hyphen as a separator.

Method 8 – Combining the Range of Values of Two Columns

Use the CONCATENATE function and the CHAR function to combine all of the ranges of the two columns, Product and State of Delivery, in a cell.

Steps:
➤ Enable the Wrap Text option for the cell where we will have the combination by selecting that cell and then going through Home Tab >> Wrap Text Option.

➤ Write the following formula in the selected cell

B4, B5, B6, B7, B8, B9 are the products of the Product column, C4, C5, C6, C7, C8, C9 are the delivery conditions of the State of Delivery column, “-” is the separator between them and CHAR(10) will create a line break between each combination of Product and State of Delivery.

After pressing ENTER, you have to drag down the plus symbol below Row 12 to increase the height of this row to fit all of the combinations.

Result:
See all of the combinations of the two columns in Row 12.

Method 9 – Using VBA Code to Concatenate Two Columns in Excel with Hyphen

Steps:
➤ Go to Developer Tab >> Visual Basic Option.

The Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.

A Module will be created.

➤ Write the following code

Function combinecolumn(x As String, y As String) As Variant

combinecolumn = x + "-" + y

End Function

It will create a function named combinecolumn and here x is for the string of the Product column, y is for the string of the State of Delivery column.

Then x, “-” and y will be combined with the “+” operator.

Go back to the sheet and write the following formula in cell D4

B4 is the Product Apple, C4 is the State of Delivery Delivered, and the combined column will combine them with a hyphen as a separator.

➤ Press ENTER and drag down the Fill Handle tool.

Result:
We have concatenated the products in the Product column and the delivery status in the State of Delivery column with a hyphen as a separator.

Download Workbook

<< Go Back to Range | Concatenate | Learn Excel