• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

Concatenate Strings in Excel

Home » Excel » Text and String Functions in Excel » Concatenate Strings in Excel

By Tejswini Bhosale Leave a Comment

concatenate-strings-in-excel

Concatenation of Strings is done by the two concatenate methods provided to us by excel which is the & operator and concatenate inbuilt function, for example, we use =”Anand”&”Singh” in a cell will give us AnandSingh as a result and similarly we use =Concatenate (“Anand”,”Singh”) will give the same result as above.

Excel Concatenate Strings (Table of Contents)

  • Concatenate String
  • Examples

Excel Concatenate String

In our earlier articles, we have seen so many examples of working with data structure and organization of data. Usually, we don’t get the structured data in place, often times we need to work on data manipulation and arrange the things in order. One such thing is combining or concatenating strings of multiple cells into one in excel.

A typical example of concatenating strings is “combining first name and last name together to create a full name”, this is the common examples we have seen using “Right”, “Left”, and “Mid” functions both in excel and VBA.

In this article, we will show you how to concatenate strings of multiple cells into one beyond the traditional example of First Name & Last Name.

Concatenate Formula in Excel

In Excel to work with concatenating cells, we have a built-in formula i.e. “CONCATENATE”. This function helps us to combine multiple cells data into one. It not only combines cell value, but we can also mention our own values to the function.

Let’s look at the syntax of the CONCATENATE Formula

concatenate syntax

The syntax is very easy to understand. Text1 is nothing but what is the first string we need to concatenate in excel, Text2 is what is the second string we need to concatenate with Text1. Like this, we can combine 255 values together.

Examples of Concatenate Strings in Excel

Below are the examples of Concatenating strings in excel.

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

Example #1

Look at the simple example of concatenating values into one. For example, in cell A1 I have “Good” and in cell A2 I have “Morning”.

concatenate example 1.1

In cell A4 we need to combine these two and create full sentence as “Good Morning”. Open concatenate formula in cell A4.

concatenate example 1.2

Select cell A1 as the first Text1.

concatenate example 1.3

As the second argument (Text2) select cell A2.

concatenate example 1.4

We have only two values to combine. So close the bracket and hit the enter key.

concatenate example 1.5

Oh, hold on…

We are lacking one thing here i.e. text separator. After the first value, we need “space” character to separate the two words.

In the above, we have selected two values so concatenate function combined these two values together. So while applying the formula after selecting the Text1 we need to insert space character as the second value to be concatenated before selecting Text2.

Popular Course in this category
Cyber Monday Sale
All in One Excel VBA Bundle (35 Courses with Projects) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)
Course Price

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course

After selecting Text1 in the Text2 argument we need to supply the space character in double quotes as ” “.

concatenate example 1.6

Once the space character inserted we will get the result as follows.

concatenate example 1.7 

Example #2

Use the Ampersand (&) Symbol as the Alternative to CONCATENATE Formula

Why do you want to use the CONCATENATE function where we can use AMPERSAND (&) symbol as an alternative?

You can use just ampersand symbol to concatenate values. For the same example of combining “Good Morning” we can use the formula as below.

concatenate example 2

After each value, we need to put the ampersand (&) symbol. This is the most commonly used method to concatenate values in excel, CONCATENATE formula is not popular among excel user thanks to ampersand symbol.

Example #3

CONCATENATE Cell Values with Manual Values

We can enter own values with cell values as well much like inserting space character. For example, look at the below data set.

concatenate example 3.1

We have a zone-wise sales value here. In the sentence column, we need to create a sentence like this –

“Total Sale of East Zone is 1500”

In the above sentence, we have only two values available with cells i.e. bold values. Remaining values we need to add as our own.

Put an equal sign in cell C2 to start the concatenation. As part of our sentence, our first value to be concatenated is “Total Sale of “(including space after).

concatenate example 3.2

Next value is our cell reference.

concatenate example 3.3

The third sentence to be concatenated is “Zone is “.

concatenate example 3.4

And the final value is cell reference.

concatenate example 3.5

Press Enter key to get the answer.

concatenate example 3.6

Drag the formula to get the concatenate values in other cells as well.

concatenate example 3.7

Example #4

CONCATENATE Date Values

Now, look at one more example of concatenating date values. Below is the data.

Concatenate Strings in Excel example 4.1

We need to combine these two values with our own sentence. Below is the sample of the same.

“Ramu Joined on 12-Jan-2018“.

Open equal sign in cell C2 and select A2 cell.

Concatenate Strings in Excel example 4.2

Next is our own sentence, so enter the text in double quotes.

Concatenate Strings in Excel example 4.3

The final cell is to concatenate is Date, so select B2 cell.

Concatenate Strings in Excel example 4.4

Press Enter Key.

Concatenate Strings in Excel example 4.5

Oh, hang on!!!! We supposed to get 12-Jan-2018 instead we got 43112.

This is the situation everybody faces whoever has learned the basic level of concatenation. When we are concatenating Date, Time, and Numbers with Formatting we need to apply the formatting with the TEXT function. So, while selecting the DATE cell select with the TEXT function.

Concatenate Strings in Excel example 4.6

Mention the format as “DD-MMM-YYYY”.

Concatenate Strings in Excel example 4.7

Press Enter key to get the answer.

Concatenate Strings in Excel example 4.8

The reason why we need to apply TEXT function to given format as a date because excel stores date & time as serial numbers. Whenever we combine we need to give formatting to them.

Drag the formula to get the concatenate values in other cells as well.

Concatenate Strings in Excel Example 4.9

Recommended Articles

This has been a guide to Concatenate Strings in Excel. Here we discuss how to concatenate strings using its formula along with examples and explanations. You can learn more about excel functions from the following articles – 

  • Extract Number from String Excel
  • How to do the Opposite of Concatenate in Excel?
  • Excel CONCATENATE Formula
  • How to Calculate in Excel Sheet?
  • Guide to Concatenate Excel Columns
  • Concatenate Strings in VBA
  • Substring Functions in Excel
  • VLOOKUP Basic Tutorial
11 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: Excel, Text and String Functions in Excel

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

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.

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

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

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

WallStreetMojo

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

WallStreetMojo

Download Concatenate Strings Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Limited Period Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More