Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Facebook Twitter Instagram
Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Tech News Today
Home»Software»Excel»How to Concatenate in Excel

How to Concatenate in Excel

Bishwo TamliBy Bishwo TamliDecember 15, 2022
how to concatenate in excel

There are a lot of situations where you may need to combine two or more values together in Excel. It could be for joining names and addresses together, forming a sentence to better represent the information, or for any other reason.

The Concatenate function does just that in excel. You can combine any value in the order of the formula, allowing you to display the information in your Excel sheet in a more concise manner. Further, you can combine up to 30 text items using this formula.

Table of Contents

  • How to Concatenate in Excel?
  • Alternatives to Concatenate in Excel
    • Using Concat Function
    • Using Ampersand

How to Concatenate in Excel?

To use the Concatenate function in Excel, you will first need to know the structure and syntax of the formula. The syntax is =Concatenate (value1, value2, value3…).

Also, you should remember a few things before using this function.

  • The = sign is there to let Excel know it is a formula.
  • The Concatenate string in the formula represents the function you’re trying to invoke.
  • The values inside the parenthesis are the values you want to join together.
  • When you concatenate a text string, you will have to include them within quotation marks.
  • When you concatenate a numeric value or cell reference, you won’t need to include them within quotation marks.
  • For joining different values, you will need to add commas after every value except the last one.
  • If you do not have specific spaces in text values, this function will join the values together without spaces.
  • The Concatenate function will always return a text value regardless of its input data type.
  • In a real-case scenario, the Concatenate formula may look like this: =CONCATENATE ("The population of ", A1, " is ", B1).

    concatenate formula 1

    In the example, the “The population of “ is a text string. The two text strings above specifically have extra spaces placed in them to accommodate for the cell references.

    If you need space between two reference cells, you will need to add a text string containing space. Here’s how it looks like:

    =Concatenate (A1, “ ”, B1)

    Alternatives to Concatenate in Excel

    Using Concat Function

    The Concat function is supposed to be the replacement for the Concatenate function in Excel. The Concat function has not entirely replaced Concatenate because the latter has been kept for backward compatibility. Older versions of Excel do not have the Concat function. Both functions perform the same functions, but Concat also has some extra features.

    Microsoft recommends using Concat, as Concatenate may not be available in future versions of Excel.

    concat_arrays_formula

    Also, Concat can recognize and combine arrays, which the others can’t. For example, the =Concat (A1:A5) formula will combine all the values within that range. To achieve the same result in Concatenate, you will need to go through the long process of typing =Concatenate (A1, A2, A3, A4, A5).

    Using Ampersand

    You can use the ampersand (&) operator as a more simple alternative to using Concatenate. The ampersand (&) operator isn’t a function but works similarly to Concatenate. Its syntax is:

    =“value”&“value”&value…

    ampersand-operator-formula-excel-2

    For example, ="The population of "&A1&" is "&B1. For joining text and numeric values, the ampersand operator follows the same principles as Concatenate.

    how-to
    Bishwo Tamli

    I'm a tech enthusiast, and I've always been troubleshooting errors by myself for years. I also like keeping up to date with the latest technology and gadgets.

    Related Posts

    check mark in excel

    How to Put Check Mark in Excel

    January 20, 2023
    best excel shortcuts

    Best 52 Excel Shortcuts Only Experts Know

    January 19, 2023
    How to Find and edit Hyperlinks on Excel

    How to Find and Edit Hyperlinks on Excel

    January 23, 2023
    how to find unique values in excel

    How to Find Unique Values in Excel

    January 15, 2023
    how to find links in excel

    How to Find and Edit External Links in Excel

    January 13, 2023
    reference isn't valid excel

    Reference Isn’t Valid Excel Error – 6 Ways to Fix it

    January 6, 2023
    Add A Comment

    Leave A Reply Cancel Reply

    Latest Posts
    how long does a cmos battery last

    How Long Does a CMOS Battery Last

    January 25, 2023
    thunderbolt vs usb c

    Thunderbolt Vs USB C: What’s the Difference

    January 25, 2023
    how to find hidden folder in laptop

    How to Find Hidden Folder in Laptop

    January 24, 2023
    You may also like
    4k-tv-as-monitor

    How to Use a 4K TV as a Monitor? (Step-by-Step Guide)

    January 26, 2023
    how-to-install-cinema-on-firestick

    How to Install Cinema on Firestick (Step-By-Step Guide)

    January 26, 2023
    how to adblock on chrome

    How to Block Ads on Google Chrome

    January 26, 2023
    Recommended
    Cookie Clicker Garden Guide

    Cookie Clicker Garden Guide to Unlocking Every Seed

    September 26, 2021
    monitor no signal

    Computer Turns On But Monitor Says No Signal (9 Ways To Fix)

    November 10, 2022
    Facebook Twitter Pinterest
    • Home
    • About Us
    • Our Team
    • Editorial Guidelines
    • Privacy Policy
    • Affiliate Disclosure
    © 2023 TechNewsToday.

    Type above and press Enter to search. Press Esc to cancel.