Concatenate in Excel: How to Concatenate Columns and Strings

-

|

What is Concatenate in Excel?

Concatenate in Excel is joining two strings into one continuous string. You can join two strings using the CONCATENATE function or the “&” (ampersand) operator.

This guide shows how to use these two methods with examples.

How to Concatenate Columns in Excel

concatenate excel

The image above shows two columns, A and B. Column C shows how to concatenate the columns using CONCATENATE function. Column D shows how the columns are combined using the “&” (ampersand) Operator.

In this section, I will demonstrate how to combine column A and column B using CONCATENATE or “&” (ampersand) Operator.

How to Concatenate in Excel with the CONCATENATE Function

The syntax of CONCATENATE Function is:

CONCATENATE(text1, [text2], [text3])

text1, text2 and text3 are the different strings you wish to combine.

Refer to the image above. In this example, I want to combine cells A and B to form the sentence “The Number of cellA are cellB”. The formula for cell C4 is shown below:

=CONCATENATE("The Number of", " ",A4, " ", "are"," ", B4)

Concatenate in Excel using the CONCATENATE function is simply adding the strings you wish to combine within the bracket “()”. Each string is then followed by a comma (,).

To add an empty space, use ” ” – quote marks with space between them.

How to Concatenate in Excel with the “&” (ampersand) Operator

To concatenate with “&” (ampersand) Operator:

On the cell you want to combine the columns, enter the equal to sign (=), followed by “&”. Unlike in CONCATENATE, with ampersand (&) each value is separated by & instead of a comma (,).

Here is the formula for cell D4:

="The Number of" & " "  & A4 & " " & "are" &" " &B4

To add an empty space use double quotes with a space.

How to Concatenate in Excel (Adding Space, Comma and Other Characters)

In the last section I demonstrated how to concatenate two columns. In this section, I will demo how to add commas, spaces and other special characters.

If you have read this guide to this point, you will notice that when I included a text string or space, I used double quotes around them.

To add an empty space, simply include two quotes with a space, ” “. You can also use the same method to include a comma or any other character.

As an example, to combine the two texts below separated by a comma and a stop:

This is how you add, followed by this.

Use the CONCATENATE formula below:

=CONCATENATE("This is how you add",",", " ", "followed by this",".")

You can achieve the same result using the & operator formula shown below:

="This is how you add" & "," & " " & " " & "followed by this" & "."

Here are the results in Excel:

combine strings with CONCATENATE Function
combine strings with & operator

More Examples of Excel Concatenate

In the last section of this guide I will show one more example.

Refer to the image above. The worksheet contains products and their prices. In the example, I combined columns A and B in C with the formula below:

=CONCATENATE(A3, " ", TEXT(B3, "$#,#0.00"))

The cells are combined with CONCATENATE. But the second cell, B3 is formatted with the TEXT function. TEXT formats the numbers in column B to include the $ sign. It also adds 2 decimal places to the numbers.

Conclusion

Concatenate in Excel is very useful if you want to join 2 strings into 1. In this guide I have demonstrated different ways to do this. I hope you found it useful.

If you have any question or comment use the “Leave a Reply” form at the end of the page. You could also share your experience with Excel concatenate.

Other Helpful Guides

Additional Resources and References

  • Was this post Helpful?
  • YesNo

LEAVE A REPLY

Please enter your comment!
Please enter your name here

ADVERTISEMENTS

FEATURED POSTS

Best Christian Movies on Netflix

The 10 Best Christian Movies on Netflix

This Itechguide reviews the 10 best christian movies on Netflix. The movies are listed in reverse order – with the 10th movie...
How to Change Orientation in Google Docs

How to Change Orientation in Google Docs

This Itechguide teaches you how to change orientation in Google Docs. The guide covers steps to change orientation in Google Docs from...
How to Delete Google Photos

How to Delete Google Photos

This Itechguide teaches you how to delete Google Photos. The guide offers steps to delete Google Photos from the Google Photos App...
How to Install Windows 10 1909 Update Manually

How to Install Windows 10 1909 Update Manually

This Itechguide teaches you how to install Windows 10 1909 update manually. Windows 10 1909 (otherwise known as Windows 10 November update)...
How to Create a Poll on Facebook

How to Create a Poll on Facebook

This Itechguide teaches you how to create a poll on Facebook. You can create a poll on Facebook from a Facebook page...

AMAZON DEALS

TRENDING POSTS

Remote Desktop Connection

Remote Desktop Connection an Internal Error Has Occurred [Fixed]

Introduction I recently received the error message "Remote Desktop Connection an Internal Error Has Occurred". It was strange because...

Find My Samsung: Register and Use Samsung Find my Mobile

Introduction Ever wondered how you could find your Samsung phone if you lost it? Find my Samsung or Samsung...
What is the Difference Between PowerShell and CMD?

Windows Powershell vs CMD: Differences and Similarities

Introduction This short guide compares Windows PowerShell vs CMD (Windows command prompt). I will cover the history and nature...
Spotify No Longer Supports this Version of Microsoft Edge

Spotify No Longer Supports this Version of Microsoft Edge [Fixed]

Introduction When you open Spotify web player on Microsoft Edge, you may receive the error message "Spotify No Longer...
Windows 10 Won't Boot

Windows 10 Won’t Boot With Black Screen? 3 Ways to Fix It

Why Won't Windows 10 Boot Up? If your Windows 10 stops with a black screen, the first question in...

ADVERTISEMENTS

BEST OF ITECHGUIDES

Disable Windows Defender in Server 2016 from Server Manager

How to Disable Windows Defender in Windows Server 2016 (3 Methods)

Introduction This guide demos 3 methods to disable Windows Defender in Server 2016. Options to...
How to Change Windows 10 Power Settings

How to Change Windows 10 Power Settings (3 Methods)

Introduction This guide demos how to change Windows 10 power settings. Options to Change Windows 10...
How to Clear Facebook Cache

How to Clear Facebook Cache

This Itechguide teaches you how to clear Facebook cache. The guide covers steps to clear Facebook cache on iPhone and Android.
Windows Cannot Find GPEDIT.MSC or GPEDIT.MSC Not Found [Fixed]

Windows Cannot Find GPEDIT.MSC or GPEDIT.MSC Not Found [Fixed]

Introduction If you try opening gpedit.msc from Windows 10 Home you will receive the error message windows cannot find...
spotify web player not working

Spotify Web Player Not Working [Fixed]

Introduction Spotify Web Player may stop working for you with the following error messages: "Spotify Web Player an Error...

RECENT POSTS

How to Change Facebook Page Name

How to Change Facebook Page Name

This Itechguide teaches you how to change Facebook page name. The guide covers steps to change Facebook page name from a PC...
How to Change YouTube Channel Name

How to Change YouTube Channel Name

This Itechguide teaches you how to change YouTube channel name. The guide covers steps to change YouTube channel name from a PC.
How to Bold Text on Facebook Post

How to Bold Text on Facebook Post

This Itechguide teaches you how to bold text on Facebook post. The guide covers steps to bold text on Facebook post from...
Best Crime Documentaries on Netflix

The 10 Best Crime Documentaries on Netflix

This Itechguide reviews the 10 best crime documentaries on Netflix. The documentaries are listed in reverse order – with the 10th documentary...
Best Netflix Series

The 10 Best Netflix Series

This Itechguide reviews the 10 best Netflix series. The series are listed in reverse order – with the 10th series on top....

AMAZON DEALS

MUST READ

PowerShell-NoTypeInformation

Powershell NoTypeInformation: Applications and Examples

Powershell NoTypeInformation: Why So Much Interest? I chose to write on 'Powershell -NoTypeInformation' because so many people ask questions...
How to Upload Video to Facebook

How to Upload Video to Facebook

This Itechguide teaches you how to upload video to Facebook. The guide covers steps to upload video to Facebook from a PC...
How to Set Google Chrome as Default Browser for Windows 10

How to Set Google Chrome as Default Browser for Windows 10

Introduction This guide demos how to set Chrome as default browser for Windows 10. Windows 10 sets Microsoft Edge...
Install and Configure Windows Deployment Services in Server 2019

Install and Configure Windows Deployment Services in Server 2019

Introduction This guide demos how to install and configure Windows Deployment Services in Server 2019 Steps...
How to Unfollow Someone on Instagram

How to Unfollow Someone on Instagram

This Itechguide teaches you how to unfollow someone on Instagram. The guide offers steps to unfollow someone on Instagram from the Instagram...

AMAZON DEALS

By using this website you agree to accept our Privacy Policy and Terms & Conditions