WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Concatenate Strings in Excel

Concatenate Strings in Excel

Excel String Concatenation

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.

Syntax

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

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

Example #1 – Basic 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 sentences as “Good Morning.”

  • Step 1: Open concatenate formula in cell A4.

concatenate example 1.2

  • Step 2: Select cell A1 as the first Text1.

concatenate example 1.3

  • Step 3: As the second argument (Text2), select cell A2.

concatenate example 1.4

  • Step 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 lack one thing here, i.e., text separator. After the first value, we need a “space” character to separate the two words.

In the above, we have selected two values, so the 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.

  • Step 5: 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 is inserted we will get the result as follows.

concatenate example 1.7 

Example #2 – Use the Ampersand (&) Symbol as the Alternative

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

You can use just an ampersand symbol to concatenate values. For the same example of combining “Good Morning,” we can use the formula 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 users thanks to the ampersand symbol.

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

Example #3 – CONCATENATE Cell Values with Manual Values

We can enter our own values with cell values as well, much like inserting space characters. 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

The 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 the 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 sentences. Below is the sample of the same.

“Ramu Joined on 12-Jan-2018″.

Open equal sign in cell C2 and select A2 cells.

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 were 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 in excel. So, while selecting the DATE cell, select 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 the Enter key to get the answer.

Concatenate Strings in Excel example 4.8

The reason why we need to apply the TEXT function to the given format as a date because of 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 concatenate formula and various other methods along with examples and a downloadable excel template. You can learn more about excel functions from the following articles – 

  • How to do the Opposite of Concatenate in Excel?
  • Excel CONCATENATE Formula
  • Concatenate Columns in Excel
  • Concatenate Strings in VBA
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 >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. 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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download Concatenate Strings Excel Template

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