• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to Join Text Together in Excel Using CONCATENATE and &

You are here: Home / Excel Formulas / How to Join Text Together in Excel Using CONCATENATE and &
How to Join Text Together in Excel Using CONCATENATE and &
March 7, 2011 by Mynda Treacy

Often data imported into Excel from another system isn’t quite in the format we want. Text can be split across columns, for example; the telephone area code below is in column A and the remainder of the number in column B.

Excel CONCATENATE function example

Here you can use the CONCATENATE function to merge cells together into one cell, or you can simply use the ampersand ‘&’ symbol to join your text.

Excel CONCATENATE Function

The syntax is:

=CONCATENATE(text1, [text2],….)

Using our example our formula will be:

=CONCATENATE(A2,B2)

And the result is:

0751547318

We can make it a bit more elegant with the addition of some symbols and spaces to separate the area code from the number like this:

=CONCATENATE("(",A2,") ",B2)

With this result:

(07) 51547318

Notice that the brackets are in quotes and I’ve added a space after the second bracket. This is because when adding characters, other text, or spaces you need to surround them in quotes.

How to Use the Ampersand to Join Text

A simpler approach to the Excel CONCATENATE function uses the ampersand, ‘&’, instead.  Using the above example our formula will be:

=("("&A2&") "&B2)

This gives us the same result, but you’ll notice that the commas are replaced with the ampersand.

I like using the ampersand for joining just a few cells together, but if you’ve got lots of cells to join or you want to add lots of additional text, symbols or spaces then CONCATENATE is a bit quicker. Especially with Excel 2007 and its predictive formulas where you only have to type out ‘=CON’ and then tab to select the first formula in the list.

predictive formula

Insert Additional Text

You can also insert additional text like this:

="Phone "&A2&" "&B2

With this result:

Phone 07 51547318

You can join as many cells together as you like, with the limit of 255 characters in a single cell.

What if you want to format the phone number with a  space like this:

(07) 5154 7318

You could join the text together using CONCATENATE or the ampersand, then paste the result as a value and apply a custom format, or you can use the LEFT and RIGHT formulas like this:

="("&A2&") "&LEFT(B2,4)&" "&RIGHT(B2,4)

With this result:

(07) 5154 7318

Enter your email address below to download the sample workbook.



By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the workbook here to practice. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

How to Join Text Together in Excel Using CONCATENATE and &

More Text Formulas Posts

Excel TEXT Function – handy but limited…or is it?

Excel TEXT Function – handy but limited…or is it?

The Excel TEXT Function converts numbers to text in the format you specify. It's hand for joining numbers and text together in custom chart labels etc.
Extract Text from a Cell using MID Formulas

Extract Text from a Cell using MID Formulas

Excel Test if a Range Contains Text, Numbers or is Empty

Excel Test if a Range Contains Text, Numbers or is Empty

Excel CLEAN Formula

Excel CLEAN Formula

The Excel CLEAN Function can help you to remove unwanted characters that are often imported when you copy data from web pages, but it has some limitations
Excel SUBSTITUTE Formula

Excel SUBSTITUTE Formula

The Excel SUBSTITUTE function replaces new text for old in a text string. It's an alternative to using Find and Replace by retaining the original data.
Excel Factor Entry 3 Re-format Data Using Formulas

Excel Factor Entry 3 Re-format Data Using Formulas

Use Excel's Text functions to re-format data imported from external sources
Excel SEARCH and You Will FIND

Excel SEARCH and You Will FIND

Excel SUBSTITUTE Function Trick

Excel SUBSTITUTE Function Trick

Excel TRIM Function Removes Spaces From Text

Excel TRIM Function Removes Spaces From Text

Excel TRIM function removes spaces from the start and end of text, while leaving spaces between words untouched.
Excel UPPER LOWER and PROPER Functions

Excel UPPER LOWER and PROPER Functions

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.




Category: Excel FormulasTag: text formulas
Previous Post:Excel Text to Columns to Correct Date FormatsExcel Text to Columns to Correct Date Formats
Next Post:Microsoft Excel’s T FunctionMicrosoft Excel’s T Function

Reader Interactions

Comments

  1. jbbordea bordeaux

    April 13, 2014 at 10:57 pm

    The ampersand puts a space before joining. I have an excel sheet that is a directory. Each contact has an email address. I want to use the & to generate a mailing list to post into outlook. For Outlook, you need to separate each email with a semicolon. So the excel format looks something like this (Row 1 is header, Col. F is the column with email addresses, Col G is the running accumulation of email addresses).
    G2=F2
    G3=G2&”; “&F3
    G4=G3&”; “&F4… and so forth
    The text string that is generated is:
    email1@abc.com
    email1@abc.com ; email2@abc.com
    email1@abc.com ; email2@abc.com ; email3@abc.com… and so forth

    The question that I have is why is the & symbol generating the space prior to the semicolon? Is concatenate a better function that would cause this not to happen?

    Reply
    • Catalin Bombea

      April 14, 2014 at 12:44 am

      Hi,
      Check the values from column F, make sure you don’t have trailing spaces. Check with LEN() function, it shoud return the same number of characters as a visual count.
      & is the same to Concatenate function, there is no difference.
      Catalin

      Reply
  2. Tony

    January 8, 2014 at 6:32 am

    I want to take the results from =CONCATENATE(text1, [text2],….)
    and be able to upload them with out the formula to my website.

    I am doing addresses that have street city state and zip in their own column. If I were to copy the results into another spreadsheet with out the original columns it would be blank.
    Thanks

    Reply
    • Catalin Bombea

      January 8, 2014 at 2:27 pm

      Hi,
      I guess you just copy and paste, which is copying the formulas. Use Paste Special, not simply paste, and paste as values.
      Catalin

      Reply
      • Tony

        January 9, 2014 at 12:09 am

        Thank You, That worked. I was going crazy trying to figure that out.

        Reply
        • Catalin Bombea

          January 9, 2014 at 2:50 am

          Glad that worked for you Tony 🙂

          Reply
  3. hope

    November 18, 2013 at 9:09 am

    I learn’t a lot of the joins but need to concatenate 2 columns, but where one of the columns only have one value, i need to remove the comma separator.
    4,
    Please advise

    Great help, thanks

    Reply
    • Catalin Bombea

      November 18, 2013 at 2:50 pm

      Hi hope,
      You can try this formula and adapt it for your needs:

      =A1&IF(LEN(B1)>0,", "&B1,"")

      This will analyze the length of text in column B and put a comma and a space only if you have values in column B.
      But if you have cases where you have values in column B and no values in column A, then the formula is:

      =IF(LEN(A1)=0,SUBSTITUTE(A1&IF(LEN(B1)>0,", "&B1,""),", ",""),A1&IF(LEN(B1)>0,", "&B1,""))

      Cheers, Catalin

      Reply
  4. mano

    October 7, 2013 at 5:15 pm

    hi mynda
    what your create menu in the worksheet(go to sheet)? please type create menu easy for training

    Reply
    • Mynda Treacy

      October 7, 2013 at 10:28 pm

      🙂 Thanks, Mano. Perhaps you’d like my Excel course. There’s one of those workbooks for every topic.

      Reply
  5. HEMANTA MAISNAM

    May 22, 2013 at 9:59 pm

    Your online training and tips are really excellent. It helps me a lot to understand the underlying ideas as your teaching method is easy understand.
    Thanks a lot.

    Reply
    • Mynda Treacy

      May 23, 2013 at 9:57 am

      Thank you, Hemanta 🙂

      Reply
  6. Lepoista

    May 1, 2013 at 11:35 pm

    Hi Mynda,

    When you have a calculated number, it often has a high degree of decimal places. How do you use concatenate to get the required number of decimal places?

    For info, I use the following formula, which is a little cumbersome, but works: int([cell]*precision+0.5)/precision where ‘precision’ is 10, 100, 1000 etc. as appropriate.

    Reply
    • Mynda Treacy

      May 2, 2013 at 6:44 pm

      Hi Lepoista,

      I would use one of the ROUND functions to get the required number of decimal places.

      Kind regards,

      Mynda.

      Reply
  7. Dana McGill

    April 6, 2013 at 12:31 am

    I know there is a way to get rid of the columns after you have completed the concatenation exercise, but I can’t seem to remember what it is. Can you tell me what that is?

    Reply
    • Carlo Estopia

      April 8, 2013 at 2:47 pm

      Hi Dana,

      What do you mean by getting rid of the columns here?

      Cheers,

      CarloE

      Reply
  8. Camilo

    January 17, 2013 at 10:30 am

    Hey, I’m having a huge problem keeping the 0 before the original number like you did in the area code.. How did you manage to keep the cell format after concatenating?

    Reply
    • Carlo Estopia

      January 20, 2013 at 12:12 pm

      Hi Camilo,

      I don’t seem to get the scenario here.Can you please explain it a different way, or send me your file with your example data via the help desk.

      Anyway, If you are using a numeric format for your number(s) then you won’t really get the 0 ahead of every number.
      I suggest you take care of the formatting or simply do this:

      Numeric in A1: 9999

      =CONCATENATE(“0”,A1)
      result: 09999

      Or if you are using area codes you can simply use the text format.
      You can do this by putting apostrophe(‘) before typing any number or you can
      right click a cell a format it to Text.

      Sincerely,

      CarloE

      Reply
  9. snustyliatt

    August 15, 2011 at 5:35 pm

    Si, probablemente lo sea

    Reply

Trackbacks

  1. Excel Formulas • My Online Training Hub says:
    September 15, 2014 at 11:42 am

    […] CONCATENATE […]

    Reply
  2. Display Missing Dates in Excel PivotTables says:
    September 9, 2014 at 1:03 pm

    […] in column B: The ampersand (&) allows us to join text strings together in the one formula. More on joining text here. More on the TEXT function here. More on Excel Tables and Structured References here. I’d like […]

    Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.