How to Concatenate Two Columns in Excel with Hyphen (9 Quick Ways)

If you are looking for some of the easiest ways to concatenate two columns in Excel with hyphen, then you will find this article useful. So, let’s get into the main article to know more about the process of this combination.

Download Workbook


9 Ways to Concatenate Two Columns in Excel with Hyphen

Here, we will use the following dataset including the records of sales of some products of a company to illustrate the ways to concatenate two columns in Excel with a hyphen.

concatenate two columns in Excel with hyphen

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Method-1: Using CONCATENATE Function to Concatenate Two Columns with Hyphen

In this section, we will use the CONCATENATE function to combine the name of the product and its corresponding delivery condition with a separator hyphen.

concatenate two columns in Excel with hyphen

Steps:
➤ Use the following formula in cell D4

=CONCATENATE(B4,"-",C4)

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

CONCATENATE function

➤ Press ENTER and drag down the Fill Handle tool.

CONCATENATE function

Result:
In this way, we have concatenated the products of the Product column and the delivery states of the State of Delivery column with a hyphen as a separator.

concatenate two columns in Excel with hyphen

Read More: How to Concatenate Columns in Excel (8 Simple Methods)


Method-2: Using CONCATENATE Function and CHAR Function

For combining the products with their corresponding delivery conditions with a separator hyphen, you can use the CHAR function with the CONCATENATE function.

concatenate two columns in Excel with hyphen

Steps:
➤ Use the following formula in cell D4

=CONCATENATE(B4,CHAR(45),C4)

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

CONCATENATE & CHAR function

➤ Press ENTER and drag down the Fill Handle tool.

CONCATENATE & CHAR function

Result:
Then, you will be able to concatenate the products of the Product column and the delivery states of the State of Delivery column with a hyphen as a separator.

concatenate two columns in Excel with hyphen

Read More: Combine Multiple Columns into One Column in Excel


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

Here, we will use the Ampersand operator for combining two columns with a hyphen as a separator.

concatenate two columns in Excel with hyphen

Steps:
➤ Type the following formula in cell D4

=B4&"-"&C4

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

Ampersand operator

➤ Press ENTER and drag down the Fill Handle tool.

Ampersand operator

Result:
After that, you will get the concatenation of the products of the Product column and the delivery states of the State of Delivery column with a hyphen as a separator.

concatenate two columns in Excel with hyphen

Related Content: How to Add Leading Zeros in Excel by CONCATENATE Operation


Method-4: Using TEXTJOIN Function to Concatenate Two Columns with Hyphen

You can use the TEXTJOIN function for combining the products and the corresponding delivery conditions with the hyphen of the following dataset.

concatenate two columns in Excel with hyphen

Steps:
➤ Type the following formula in cell D4

=TEXTJOIN("-",TRUE,B4,C4)

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

TEXTJOIN function

➤ Press ENTER and drag down the Fill Handle tool.

TEXTJOIN function

Result:
Finally, we will combine the products of the Product column and the delivery states of the State of Delivery column with a hyphen as a separator.

concatenate two columns in Excel with hyphen

Read More: How to Concatenate Multiple Cells With Space in Excel (7 Methods)


Similar Readings:


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

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

concatenate two columns in Excel with hyphen

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

flash fill feature

➤ Then start typing the combination for the second row like the previous one.
After that, the suggestions for the remaining rows will appear with the help of the Flash Fill feature as the following figure.
➤ Finally, press ENTER.

flash fill feature

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

concatenate two columns in Excel with hyphen

Read More: Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)


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

In this section, we will show the way to combine the text strings of the Product column and the numeric values of the Sales column with a hyphen by using the CONCATENATE function and the TEXT function. You can use the Ampersand Operator or the TEXTJOIN function instead of the CONCATENATE function like the previous methods also.

concatenate two columns in Excel with hyphen

Steps:
➤ Use the following formula in cell D4

=CONCATENATE(B4,CHAR(45),TEXT(C4,"$ 0,000.00"))

Here, 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.

combining texts and numeric values

➤ Press ENTER and drag down the Fill Handle tool.

combining texts and numeric values

Result:
Finally, you will be able to unite the products of the Product column and the sales values of the Sales column with a hyphen as a separator.

combining texts and numeric values

Read More: How to Combine Text from Two or More Cells into One Cell in Excel (5 Methods)


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

Here, we will concatenate text strings of the Product column and the date values of the Delivery Date column with a hyphen.

concatenate two columns in Excel with hyphen

Steps:
➤ Type the following formula in cell D4

=CONCATENATE(B4,"-",TEXT(C4,"m/d/yyyy"))

Here, 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.

combining texts and date values

➤ Press ENTER and drag down the Fill Handle tool.

combining texts and date values

Result:
Eventually, you will get the concatenation of the products of the Product column and the dates of the Delivery Date column with a hyphen as a separator.

concatenate two columns in Excel with hyphen

Read More: How to Combine Date and Text in Excel (5 Ways)


Method-8: Combining Range of Values of Two Columns

We will try to combine all of the ranges of the two columns; Product and State of Delivery in a cell by using the CONCATENATE function and the CHAR function.

concatenate two columns in Excel with hyphen

Steps:
➤ Firstly, you have to 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.

combining range of values of two columns

➤ Write the following formula in the selected cell

=CONCATENATE(B4,"-",C4,CHAR(10),B5,"-",C5,CHAR(10),B6,"-",C6,CHAR(10),B7,"-",C7,CHAR(10),B8,"-",C8,CHAR(10),B9,"-",C9)

Here, 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.

combining range of values of two columns

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.

combining range of values of two columns

Result:
So, now we can see all of the combinations of the two columns in Row 12.

concatenate two columns in Excel with hyphen

Read More: How to Concatenate Range in Excel (For Both Old and New Versions)


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

Here, we will do the concatenation of the two columns; Product and State of Delivery by using a VBA code.

concatenate two columns in Excel with hyphen

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

VBA Code

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.

VBA Code

After that, a Module will be created.

concatenate two columns in Excel with hyphen

➤ 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.

VBA Code

Now, go back to the sheet and write the following formula in cell D4

=combinecolumn(B4,C4)

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

VBA Code

➤ Press ENTER and drag down the Fill Handle tool.

VBA Code

Result:
In this way, we have concatenated the products of the Product column and the delivery states of the State of Delivery column with a hyphen as a separator.

concatenate two columns in Excel with hyphen

Related Content: VBA to Concatenate Range with Separator in Excel (3 Ways)


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Conclusion

In this article, we tried to cover the ways to concatenate two columns in Excel with hyphen easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo