EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources TEXT and String Functions in Excel Concatenate Strings in Excel
Secondary Sidebar
Excel Functions
  • TEXT and String Functions in Excel
    • Excel CODE Function
    • Excel Find
    • TRIM Formula in Excel
    • Excel CHAR Function
    • LEFT Formula in Excel
    • Opposite of Concatenate in Excel
    • Concatenate Strings in Excel
    • Extract Numbers From String
    • VALUE Formula in Excel
    • Left TRIM in Excel
    • Substring Excel Function
    • SUBSTITUTE Function in Excel
    • Excel VALUE Function
    • Excel REPT Function
    • Excel TRIM Function
    • Excel CLEAN Function
    • Excel EXACT Function
    • PROPER Function in Excel
    • Excel LEFT Function
    • MID Function in Excel
    • LEN Function in Excel
    • CONCATENATE Function in Excel
    • SEARCH Function in Excel
    • RIGHT Function in Excel
    • FIND Function in Excel
    • TEXT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (36+)
  • Maths Function in Excel (47+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (220+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Online COURSE
  • Online Excel Data Analysis Training

Concatenate Strings in Excel

By Madhuri ThakurMadhuri Thakur

concatenate strings in Excel

Excel Concatenate Strings (Table of Contents)

  • Introduction to Concatenate Strings in Excel
  • How to Use Concatenate Strings in Excel?

Introduction to Concatenate Strings in Excel

Concatenate in Excel is used to 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 where we want to combine the data of two or more columns in one column or a row. Concatenate is very helpful in structuring the data according to our requirements. It combines strings and text together 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.

Start Your Free Excel Course

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

Concatenate can join up to 30 text items and combine them 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 first and last names of employees by using concatenate function.

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

Example #1 – CONCATENATE using Formula Tab in Excel

In the below image, we have two columns of first and last name.

concatenate string 1

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

Go to Column C2.

concatenate string 1-1

Select 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 select CONCAT Function 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 gives reference to cell B2, which is the Last Name, Text 2-Put Comma inside semicolons and in Text 3, reference to 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 for 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 Text2.
  • Give the reference of cell C6 for Text3.
  • Enter comma”,” in-between the semicolons for Text4.
  • Give the reference of cell D6 for Text5.
  • Enter comma”,” in-between the semicolons for Text6.
  • 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 way to concatenate is by using the “&” operator instead of concatenate function. Let us take the same example that we used in Example 1, where we want to join the first name and the last name.

Concatenate Strings in Excel 3-1

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

Concatenate Strings in Excel 3-2

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

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 like this “The invoice of certain $ value was due on this Month”. To get such comments in column D, we need to follow the below steps. Go to cell D2 and Enter CONCATENATE function as below.

CONCAT 4-2

Now Insert the Comment “The invoice of $” in 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

Give the comment “was due on” with one space in between the semicolon as Text3.

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, and You can see the final comment in cell D2 as 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 the CONCAT. The Concatenate function will still work as it is still kept for backward compatibility. However, we don’t 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 in the form of 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 along with 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
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel Data Analysis Training (17 Courses, 8+ Projects)4.8
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*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?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Concatenate Strings Excel Template

EDUCBA

डाउनलोड Concatenate Strings Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more