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.

Advertisement

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.

Advertisement

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here

FEATURED POSTS

network discovery keeps turning off server 2016

How to Fix Network Discovery If it Keeps Turning Off in Server 2016

Does network discovery keep turning off in your Windows server 2016? It is likely that one of its dependent services is not...
how to install windows 10 1903 update manually

How to Install Windows 10 1903 Update Manually

Windows 10 1903 Update was released in May, 2019. But some users are not yet offered the update via automatic update. The...
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...
DISM.exe /Online /Cleanup-Image /Restorehealth

DISM.exe /Online /Cleanup-Image /Restorehealth Explained

What is DISM.EXE /Online /Cleanup-image /RestoreHealth? "DISM.exe /Online /Cleanup-Image /Restorehealth" is a DISM command that repairs issue with the...
DHCP Relay agent

DHCP Relay Agent: Configuration in Windows Server 2016

What is a DHCP Relay Agent? A DHCP Relay Agent allows DHCP clients in a different network subnet to...

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

BEST OF ITECHGUIDES

Windows defender blocked by Group Policy

Windows Defender Blocked by Group Policy [Fixed]

Introduction If you receive the error message Windows Defender is blocked by group policy, your computer may have been...
spotify subscription

How to Subscribe to Spotify Premium

Introduction Paid Spotify subscription (premium) has a lot more benefit compared to the free one. Some of the benefits...
scanning and repairing drive

Scanning and Repairing Drive Issue on Windows 10 [Fixed]

Introduction A number of Windows 10 users have reported that when they boot their computer it is stuck on...
Dual WhatsApp account in one phone

How to Use Dual WhatsApp Accounts in One Android Phone

Introduction Most Android phones now support dual SIM. If your Android phone has 2 SIM cards you can configure...
WhatsApp PC

How to Use WhatsApp on Your PC

Introduction Wondering whether you can use WhatsApp on your PC? Yes you can! Since I started using WhatsApp on...

RECENT POSTS

disable cortana windows 10 featured

How to Disable Cortana in Windows 10 (2 Methods)

Introduction Some Windows 10 users may not like Cortana. Solution? Disable Cortana. You are probably reading this because you...
bootrec /fixboot access is denied

How to Fix BootRec /FixBoot Access is Denied Error in Windows 10

Introduction BootRec /FixBoot Access is Denied Error in Windows 10? This error is likely caused by corrupt EFI directory.
Reset Windows 10

How to Reset Windows 10 (2 Methods)

Introduction If your Windows 10 is broken, one available way to fix it is to use Windows 10 reset....
system restore windows 10

How to Enable and Use System Restore in Windows 10

Introduction System restore in windows 10 is a very important tool that is probably ignored by most users. But...
windows has stopped this device code 43

How to Fix Windows Has Stopped this Device Code 43 Error

Introduction Sometimes you may receive "Windows has stopped this device code 43" error. This error is likely to come...
Windows could not automatically detect this network's proxy settings

How to Fix “Windows Could not Automatically Detect this Network’s Proxy Settings”

Introduction You suddenly lose the ability to connect to the internet. Then you run network troubleshooter and it returns...
printer offline

5 Ways to Restore Your Printer Online If Status is Offline

Introduction Is your printer offline? Setting it online is very simple but sometimes it is more than just setting...

How to Fix “BootMgr is Missing” Error in Windows 10

Introduction If you receive Fix "BootMgr is missing" Error in Windows 10, the default response is panic! But you...
windows 10 search featured

How to Fix Windows 10 Search If it Stops Working

Introduction Are you having troubles with Windows 10 search? Apparently, it is a fairly common problem with a number...
oooops, something went wrong. reload

How to Fix “Oooops something went wrong. reload” Spotify Error

Introduction If you receive "Oooops something went wrong. reload" Spotify error, do not panic. The fix is simpler than...

MUST READ

Websites Like Craigslist for Cars - featured

Top 5 Websites Like Craigslist for Cars

Craigslist is a local classified Ads website with 7 sections including cars. It is a brilliant site but using other websites like...
bullet points in excel

4 Easy Ways to Insert Bullet points in Excel

Introduction Inserting bullet points in excel is not as straightforward as adding bullet points in Word. This guide demonstrates...
Spotify no Longer Supports this Version of Firefox

How to Fix “Spotify no Longer Supports this Version of Firefox”

Introduction Have you been frustrated by "Spotify no Longer Supports this Version of Firefox" error? You are not alone!...
Transfer Schema Master Role

Transfer Schema Master Role With MMC or NTDSUTIL Command Tool

Introduction There are situations when an administrator may need to transfer the schema master role from one Domain Controller...
Wifi doesn't have a valid IP configuration

How to Fix “Wifi Doesn’t Have a Valid IP Configuration” Error

Introduction "Wifi doesn't have a valid IP configuration" error is common among Windows users. When you receive this error...

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