EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources TEXT and String Functions in Excel Concatenate Strings in Excel
 

Concatenate Strings in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated May 31, 2023

concatenate strings in Excel

 

 

Introduction to Concatenate Strings in Excel

Concatenate Strings in Excel combine values from several cells in one cell or join different pieces of text in one cell. This function is mostly used where data is not structured in Excel, and we want to combine the data of two or more columns in one or a row. Concatenate is very helpful in structuring the data according to our requirements. It combines strings and text and is a built-in function in Excel. Concatenate is replaced as CONCAT from Excel 2016 and newer versions. It is known as the Text/String function as well.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Concatenate can combine up to 30 text items to make one text.

Syntax:

concatenate string syntax

How to Use Concatenate Strings in Excel?

There are multiple ways to use the concatenate function. We will take a look at them with the help of some formulas.

Let’s see an example below where we need to join the employees’ first and last names using the concatenate function.

You can download this Concatenate Strings Excel Template here – Concatenate Strings Excel Template

Example #1 – CONCATENATE using Formula Tab in Excel

We have two columns of first and last names in the image below.

concatenate string 1

Now we would join their first and last names to get the complete names using the concatenate function. Let’s see the steps to insert the Concatenate Function to Join the First and Last Names.

Go to Column C2.

concatenate string 1-1

Select the Formulas Icon and click on Insert Function, as shown below.

concatenate string 1-2

Then select a Category as “TEXT” and then select the Function category will open in which you can choose Function CONCAT and Click OK.

concatenate string 1-3

A window will open, as shown below, after clicking OK, where you can type the text you want to concatenate.

concatenate string 1-4

For column C2, in-text 1 references cell B2, which is the Last Name, Text 2-Put Commas inside semicolons, and Text 3, references cell B1, which is the first name.

concat string 1

Click on the OK button.

concat string 1-1

The last name followed by the first name will be concatenated in column C2.

concat string 1-2

Drag down the formula till the end of the data, and the formula will be applied to the rest of the data.

concatenate string in excel 1

Example #2 – CONCATENATE Using Direct Formula

As you can see in the below screenshot, we need to concatenate the text in Column A (First Name), Column B (Last Name), Column C (Address), Column D (City), and Column E(Zipcode) to arrive at the Mailing address in Column F.

Concatenate Strings in Excel 1-1

We can do this by simply entering the function in cell F6.

Steps for Entering the Concatenate Function

  • Type “=CONCATENATE(“ in cell H6.
  • Now give the reference of cell B6 for Text1.
  • Enter space in between the semicolons for Text 2.
  • Give the reference of cell C6 for Text3.
  • Enter comma”,” in between the semicolons for Text 4.
  • Give the reference of cell D6 for Text5.
  • Enter comma”,” in between the semicolons for Text 6.
  • Give the reference of cell E6 for Text7.
  • Enter dash”-” in between the semicolons for Text8.
  • Give the reference of cell F6 for Text9.

Concatenate Strings in Excel 1-2

Press Enter Key after entering the formula.

Concatenate Strings in Excel 1-3

Drag down the formula in the below cells to get the mailing address details of other rows.

concatenate string in excel 2

As you can see in the above screenshot, we have the mailing address in column H for all the persons.

Example #3 – CONCATENATE Using “&” Operator

The “&” operator instead of concatenate function is the way to concatenate. Let us take the same example we used in Example 1, where we want to join the first and last names.

Concatenate Strings in Excel 3-1

To concatenate the text strings in columns A and B with the help of the “&” operator, we must follow the steps below. Go to cell C2 and give a cell reference to cell B2, as shown in the below screenshot.

Concatenate Strings in Excel 3-2

Now Insert the “&” operator, comma, and space between the semicolons”, “as shown in the screenshot below.

Concatenate Strings in Excel 3-3

Now again, Insert the “&” operator and give the reference of cell A2 and close the bracket.

concat string example 3-4

Press the Enter Key, and you will get the desired result.

concat string example 3-5

Drag the formula till the end of the data.

concatenate string in excel 3

Example #4 – CONCATENATE Using Calculated Field

We can concatenate the text string even by calculating a certain field. Suppose you have the data of a few customers, and you need to enter certain comments for those customers, as given in the below screenshots.

CONCAT 4-1

The comment we need in column D is, “The invoice of certain $ value was due on this Month”. We need to follow the steps below to get such comments in column D. Go to cell D2 and Enter CONCATENATE function as below.

CONCAT 4-2

Now Insert the Comment “The invoice of $” between the semicolon as Text1.

CONCAT 4-3

Now give the reference of cell C2, which is the invoice amount value for Text2.

CONCAT 4-4

Comment “was due on” with one space between the semicolon as Text 3.

CONCAT 4-5

Give the cell reference of cell B2, which is the invoice due Month as Text4. Close the bracket.

CONCAT 4-6

Press the Enter Key to see the final comment in cell D2 below.

CONCAT 4-7

Drag down the formula for the rest of the data.

concatenate string in excel 4

Things to Remember About Concatenate Strings in Excel

  • Concatenate function requires at least one argument to work.
  • From Excel 2016 or Newer Version, CONCATENATE is replaced with CONCAT. The Concatenate function will still work as it is kept for backward compatibility. However, we don’t think it will be kept in future versions, so it is highly recommendable to use CONCAT instead of CONCATENATE.
  • The result of the concatenate function is always a Text String. Even if you use numbers to concatenate, the result will always be a text string.
  • In Concatenate, each cell reference needs to be listed separately; it doesn’t recognize arrays.
  • In CONCATENATE Function, you can use 8192 characters which means you can concatenate up to 255 Strings.
  • The formula will give #Value! Error if anyone’s argument is invalid.
  • The difference between the concatenate function and using the “&” operator to concatenate text string is that there is no limitation of 255 strings while using the “&” operator. Apart from that, there is no difference.

Recommended Articles

This is a guide to Concatenate Strings in Excel. Here we discuss how to use concatenate strings in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles to learn more–

  1. Concatenation in Excel
  2. Opposite of Concatenate in Excel
  3. VBA Concatenate Strings
  4. Excel Concatenate Date

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Concatenate Strings Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Concatenate Strings Excel Template

EDUCBA

डाउनलोड Concatenate Strings Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW