7 Ways to Combine Two Cells in Excel with a Dash

Excel is a powerful tool for organizing and analyzing data, and one of its many features is the ability to combine two cells with a dash. This can be useful for creating unique identifiers, concatenating data from different cells, or simply making your data more readable. In this article, we’ll explore 7 different ways to combine two cells in Excel with a dash and show you step-by-step how to do it efficiently.

Combine Two Cells with a Dash


📁 Download Excel File

Download this file to practice.


Learn to Combine Two Cells in Excel with a Dash with These 7 Methods

In this article, we will demonstrate how users can combine two cells in Excel with a dash in 7 simple methods. The methods include using functions such as CONCATENATE, CONCAT, TEXTJOIN, and TEXT. We will also employ just the ampersand operator (&), which will help us combine two cells without any functions. We will also see the efficiency of Power Query Tool and create our own VBA macro code to combine two cells with a dash in between. As a bonus, we have also demonstrated how a date can be joined with text using the TEXT function. In this venture of ours, we choose a simple dataset of FIFA World Cup Winners from 2002-2022. So, let’s have fun with the central part of our article.

Dataset

Method 1

1. Using Ampersand Operator

One way to combine two cells in Excel with a dash() is to use the ampersand operator. The ampersand operator, represented by the “&” symbol, is used to concatenate (or join) text from different cells. Here’s how to use it:

⬇️⬇️ STEPS ⬇️⬇️

  • Select the cell where you want the combined text to appear. We are selecting D6 and typing the following formula in the formula bar.

 =B6&"-"&C6

Using Ampersand to Combine Two Cells with a Dash

  • Then, press Enter. The text from cells B6 and C6 will be combined with a dash in between.

Formula result

  • Finally, drag the formula down to the other cells by using Fill Handle.

Method 1 final result after fill handle

Method 2

2. Applying CONCATENATE Function

Another way to combine two cells in Excel with a dash is to use the CONCATENATE function. The CONCATENATE function is a built-in Excel function that allows you to join text from different cells. The result of the CONCATENATE function, which may combine up to 30 items, is returned as text. It accepts up to 30 text-based parameters, such as text1, text2, text3, etc. Values can be provided as hard-coded text strings or cell references. Here’s how to use it:

⬇️⬇️ STEPS ⬇️⬇️

  • Select the cell where you want the combined text to appear. We are selecting D6 and typing the following formula in the formula bar.

=CONCATENATE(B6,"-",C6)

Using CONCATENATE to Combine Two Cells with a Dash

  • Then, press Enter. The text from cells B6 and C6 will be combined with a dash in between.

CONCATENATE Formula result

  • Finally, drag the formula down to the other cells by using Fill Handle.

Method 2 final result after fill handle

Method 3

3. Utilizing CONCAT Function

The next method we will explore to combine two cells in Excel with a dash is the CONCAT function. The CONCAT function is similar to CONCATENATE function. Essentially, it replaces the CONCATENATE function in Excel 2019 and later versions. Multiple arguments with the names text1, text2, text3, etc. are accepted by the CONCAT function up to a maximum of 255. Cell references, ranges, and text strings with hard codes can all be used as arguments. Values are concatenated in the order they occur, with only the first argument being necessary.  Here’s how to use it:

⬇️⬇️ STEPS ⬇️⬇️

  • Select the cell where you want the combined text to appear. We are selecting D6 and typing the following formula in the formula bar.

=CONCAT(B6,"-",C6)

Using CONCAT to Combine Two Cells with a Dash

  • Then, press Enter which will combine the text from cells B6 and C6 with a dash in between.

CONCAT Formula result

  • Finally, drag the formula down to the other cells by using Fill Handle.

Method 3 final result after fill handle

Method 4

4. Employing TEXTJOIN Function

Another function similar to CONCATENATE function, which joins multiple cells’ content into one, is TEXTJOIN function. With or without a delimiter, this function concatenates many values together. With the option to disregard empty cells, TEXTJOIN may concatenate values supplied as cell references, ranges, or constants. Delimiter, ignore_empty, and text1 are the three prerequisite parameters for the TEXTJOIN function.

A delimiter, also known as the text to be used between values that are concatenated together, is the text that should be surrounded by double-quotes (“”), such as a space (” “), a comma with (“, “) or maybe a dash (“-“) in our case. Provide an empty string to use no delimiter (“”). Empty values can either be ignored or included in the result by setting the Boolean variable Ignore_empty to TRUE or FALSE. To prevent delimiters with no content in the TEXTJOIN result, this is frequently set to TRUE. The first value to be combined is Text1. This might be a hard-coded text value, a range, or a cell reference. Text2, Text3, Text4, and any optional parameters can each have a maximum of 252 values. Since our delimiter will be a dash, we will put (“-“) in the delimiter section of the function.

⬇️⬇️ STEPS ⬇️⬇️

  • Select the cell where you want the combined text to appear. We are selecting D6 and typing the following formula in the formula bar.

=TEXTJOIN("-",TRUE,B6,C6)

Using TEXTJOIN to Combine Two Cells with a Dash

  • Then, press Enter which will combine the text from cells B6 and C6 with a dash in between.

TEXTJOIN Formula result

  • Finally, drag the formula down to the other cells by using Fill Handle.

Method 4 final result after fill handle

Method 5

5. Using Power Query Tool

Excel has a business intelligence tool called Power Query that enables you to import data from several sources and then clean, modify, and restructure the data as necessary. It enables you to create a query once and reuse it with a straightforward refresh. It has a decent amount of functionality. We can import and clean Millions of rows by Power Query into the data model for further analysis. The user interface is clear and well-designed, making it simple to learn. The good thing about it is that none of it requires you to know or use any code. We can also create a new column and concatenate the existing ones onto the new one using the Power Query Tool.

Now let’s go make address cards using the Power Query Tool.

⬇️⬇️ STEPS ⬇️⬇️

  • Choose the range you want to combine. We will select the range B5:C11. That’s right, and we will select the header as well.
  • Next, go to the Data tab and select the From Table/Range option under the Get & Transform Data group.

Using Power Query to Combine Two Cells with a Dash

  • Since our selected range was not in a Table format, Excel will first convert it into a table. First row is the header, so to recognize that, we need to check the box behind My Table has headers. Then press OK.

Confirming Data range

  • Next, Power Query Editor dialogue box will show up. Since we need to add another column which will be the merge of our existing columns, we will select the two columns and select Merge Columns from From Text group of Add Column tab.

Merging Columns

  • The Merge Columns window will show up. It will give us the option put a separator and Name our new merged column. In the Separator box, there is no dash, so we will select Custom and, subsequently, add a dash(-) in the box. We also named our newly formed column, but this is optional. Then, press OK.

Merge Column window

As a result, both columns have been joined together, as shown in the image.

Power Query Result

  • Finally, to load the new column, select Close & Load from Home tab.

Closing Power Query

The new Table with the merged column and the dash will show up on a new worksheet.

Loaded Power Query result

Method 6

6. Creating VBA Code

VBA (Visual Basic for Applications) macros are a powerful tool that can automate repetitive tasks in Excel. One of the tasks that can be automated is combining two cells in Excel with a dash (). By using VBA macros, you can quickly combine two cells with a dash in a worksheet or range, saving you time and making your data more organized and readable. In this section, we will explore how to use VBA macros to combine two cells with a dash in Excel.

⬇️⬇️ STEPS ⬇️⬇️

  • Pressing Alt+F11 will open Microsoft Visual Basic, where we insert a Module from the Insert tab to open a whiteboard.

Inserting Module to Combine Two Cells with a Dash

  • Now copy and paste the following code into the module.
Sub Combinecellswithdash()
    Dim WI As Long
    WI = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    For i = 6 To WI
        Cells(i, 4).Value = Cells(i, 2).Value & "-" & Cells(i, 3).Value
       Next i
End Sub

Code to Combine Two Cells with a Dash

  • Finally, press F5 to execute the code and combine two cells with a dash().

Method 6 Final Result

Method 7

7. Combining Text & Date

If one cell contains text and another contains a date, you can combine them with a dash in Excel using the CONCATENATE or CONCAT function, as well as the TEXT function. The Text function converts a date value to text in a specific format. If you don’t use the TEXT function when combining a cell with text and a cell with a date in Excel, the date will be displayed as a serial number instead of the date format. The serial number is the number of days since January 1, 1900.

For example, if cell A1 contains the text “Text” and cell B1 contains the date “1/1/2022”, the formula =CONCATENATE(A1,”-“,B1) will return “Text-42938” instead of “Text-01/01/2022”.

Using the TEXT function ensures that the date is displayed in the desired format rather than as a serial number. It also allows you to use any date format that you want by replacing the format inside the Text function.

Here’s how to use it:

⬇️⬇️ STEPS ⬇️⬇️

  • Select the cell (D6) where you want the combined text to appear. Type the following formula in the formula bar.

 =CONCAT(B6,"-",TEXT(C6,"mm/dd/yyyy"))

Using CONCAT to Combine Two Cells with dates with a Dash

🔨 Formula Breakdown

👉  Firstly, the TEXT function will convert the serial number of the date to the given format.

👉  Next, the CONCAT function joins the text and date in the desired format.

  • Press Enter, which will combine the text from cell B6 with the date from cell C6 in the format of “mm/dd/yyyy” with a dash in between.

combining cells with dates with a Dash result

  • Finally, drag the formula down to the other cells by using Fill Handle.

Method 7 final result


How to Combine Two Columns in Excel with a Dash

We can combine two columns in Excel with a dash () using any of the previously described methods. For this section, we will use VBA macro to join two columns with a dash. We simply can’t join two columns at once using a function. By using VBA, we don’t have to join two cells first and then do the same for other rows by using Fill Handle. We can do the entire thing with just one code. In this section, we will explore how to use VBA macros to combine two columns with a dash in Excel.

⬇️⬇️ STEPS ⬇️⬇️

  • Pressing Alt+F11 will open Microsoft Visual Basic, where we insert a Module from the Insert tab to open a whiteboard.

Inserting Module to Combine Two columns with a Dash

  • Now copy and paste the following code into the module.
Sub Combinecolumnswithdash()
    Dim WI As Long
    WI = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    For i = 6 To WI
        Cells(i, 4).Value = Cells(i, 2).Value & "-" & Cells(i, 3).Value
       Next i
End Sub

Code to Combine Two columns with a Dash

  • Finally, press F5 to execute the code and combine two columns with a dash().

VBA result

There you go. Columns B and C have been combined into one with a dash () in between.


How to Combine Two Cells in Excel with a Slash

To combine two cells in Excel with a slash(/), you can use any method explained above. All you have to do is exchange the dash() with a slash(/). You can use any functions we demonstrated previously, but in this example, we will use the TEXTJOIN function for simplicity. So, let’s see how we can combine two cells in Excel with a Slash.

⬇️⬇️ STEPS ⬇️⬇️

  • Select the cell where you want the combined text to appear. We select D6 and type the following formula in the formula bar.

=TEXTJOIN("/",TRUE,B6,C6)

Using TEXTJOIN to Combine Two Cells with a Slash

  • Then, press Enter which will combine the text from cells B6 and C6 with a slash(/) in between.

result of formula

  • Finally, drag the formula down to the other cells by using Fill Handle.

using Fill Handle


📝 Takeaways from This Article

📌  Firstly, we used Ampersand operator(&) to combine cells with a dash(-) in between.

📌  Then, we demonstrated how users could apply to CONCATENATE function to concatenate texts.

📌  Next, we employed the CONCAT function to combine two cells.

📌  We then move on to explaining the TEXTJOIN function capability to do the same.

📌  Again, we showed how we could achieve a similar task using Power Query tool.

📌  Then, we created a macro for every task of today’s lesson in one go using VBA script.

📌  Lastly, we explained how to combine two cells with one of them containing dates.


Conclusion

We can accomplish combining cells with a dash in Excel is a simple task in a variety of ways. With the help of this article, you’ll be able to choose the method that works best for your data and your workflow. You’ll be able to combine two cells in Excel with a dash in a more efficient way. The step-by-step instructions provided will help you achieve your desired result with ease. If you have any questions, feel free to comment below. I will try to answer them as soon as possible. Have fun using Excel, and you can visit Excelden.com for more tutorials. Thank You.

(Visited 21 times, 1 visits today)
Hassan Shuvo

Hassan Shuvo

Hello, I am Mehedi Hassan Shuvo.I am an Engineering graduate from Bangladesh University of Engineering and Technology. I love reading Thriller books, watching Anime, and playing Cricket. I also love learning about new software. Excel is one of my favorite ones by far. My efforts will be worthwhile if I can utilize my expertise to assist anyone. If you find any faults in my writing please let me know.I will try to correct them as I am learning everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo