MALIKA KAROUM

MALIKA KAROUM

  • Home
  • Inleiding
  • Unite Arab Emirates
  • Blog
  • video’s
  • Promotion
    • Worth for free now
    • Work from Home 2023
    • Gadgets
    • All about Windows
    • about Whatsapp
    • Whats the
    • About websites
    • New Ways
    • New Way of Watching
    • Virtual
    • Website
    • All about Video
    • How to Use
    • YouTube Info
    • All about Twitter
    • The Best of
    • About Apps
    • Google News
    • For Free
    • About This
    • Need More
    • Why should you
    • Iphone news
    • Interesting News
    • About Amazone
    • Some tips
    • About Netflix
    • All about Music
    • About Facebook
  • Marketing
    • Malika Karoum Strategie Modules
    • Malika Karoum Online Marketing
    • Malika Karoum Business Service
    • Malika Karoum Marketing Platform
    • Online business marketing
  • Luxury
    • The Indulgence Business site
    • The Luxury Web site
    • The Ultimate Indulgence
    • The Indulgence Site
    • The Ultimate Luxury Information site
    • Online luxury
  • Malika Karoum
    • Malika Karoum LinkedIn
    • Malika Karoum Facebook
    • Malika Karoum Instagram
    • Malika Karoum Business News
    • Adverteren grote fraude
    • Menu POS
    • Malika Karoum Evenementen
  • Security
  • Malika Karoum link
  • Home
  • Malika Karoum Global News
  • How to Combine Two Columns in Excel Easily and Quickly
February 3, 2023

How to Combine Two Columns in Excel Easily and Quickly

How to Combine Two Columns in Excel Easily and Quickly

by Malika Karoum / Wednesday, 29 July 2020 / Published in Malika Karoum Global News
combine-columns

If you’re using Excel and have data split across multiple columns that you want to combine, you don’t need to manually do this. Instead, you can use a quick and easy formula to combine columns.

We’re going to show you how to combine columns in Excel using the ampersand symbol or the CONCAT function. We’ll also offer some tips on how to format the data so that it looks exactly how you want it.

How to Combine Columns in Excel

There are two methods to combine columns in Excel: the ampersand method and the concatenate formula. In many cases, using the ampersand method is quicker and easier than the concatenate formula. That said, use whichever you feel most comfortable with.

1. Combine Columns with the Ampersand Symbol

excel combined cells ampersand

  1. Click the cell where you want the combined data to go.
  2. Type =
  3. Click the first cell you want to combine.
  4. Type &
  5. Click the second cell you want to combine.
  6. Press the Enter key.

For example, if you wanted to combine cell A2 and B2, the formula would be: =A2&B2

2. Combine Columns with the CONCAT Function

excel combined cells CONCAT

  1. Click the cell where you want the combined data to go.
  2. Type =CONCAT(
  3. Click the first cell you want to combine.
  4. Type ,
  5. Click the second cell you want to combine.
  6. Type )
  7. Press the Enter key.

For example, if you wanted to combine cell A2 and B2, the formula would be: =CONCAT(A2,B2)

This formula used to be CONCATENATE, rather than CONCAT. While the former still works, it is being depreciated and you should use the latter to ensure compatibility with current and future versions of Excel.

More Than Two Cells

You can combine as many cells as you want using either method. Simply repeat the formatting like so:

  • =A2&B2&C2&D2 … etc.
  • =CONCAT(A2,B2,C2,D2) … etc.

Combine the Entire Column

Once you have placed the formula in one cell, you can use this to automatically populate the rest of the column. You don’t need to manually type in each cell name that you want to combine.

To do this, double click the bottom right corner of the filled cell. Alternatively, left click and drag the bottom right corner of the filled cell down the column.

Tips on How to Format Combined Columns

Your combined Excel columns could contain text, numbers, dates, and more. As such, it isn’t always suitable to leave the cells combined without formatting them.

To help you out, here are various tips on how to format combined cells. In our examples, we’ll refer to the ampersand method, but the logic is the same for the CONCAT formula.

1. How to Put a Space Between Combined Cells

excel combined cells with space

If you had a “First name” column and a “Last name” column, you would want a space between the two cells.

To do this, the formula would be: =A2&” “&B2

This formula says to add the contents of A2, then add a space, then add the contents of B2.

It doesn’t have to be a space. You can put whatever you want between the speech marks, like a comma, a dash, or any other symbol or text.

2. How to Add Additional Text Within Combined Cells

excel combined cells with additional text

The combined cells don’t just have to contain their original text. You can add whatever additional information you want.

Let’s say cell A2 contains someone’s name (e.g. Marge Simpson) and cell B2 contains their age (e.g. 36). We can build this into a sentence that reads “The character Marge Simpson is 36 years old”.

To do this, the formula would be: =”The character “&A2&” is “&B2&” years old”

The additional text is wrapped in speech marks and followed by an &. When referencing a cell, these don’t need speech marks. Note that you also need to include where you want the spaces to be—hence “The character ” with a space at the end, rather than “The character”.

3. How to Correctly Display Numbers in Combined Cells

excel combined cell with text formula

If your original cells contain formatted numbers like dates or currency, you’ll notice that the combined cell strips the formatting.

You can solve this with the TEXT function, which you can use to define the required format.

Let’s say cell A2 contains someone’s name (e.g. Marge Simpson) and cell B2 contains their date of birth (e.g. 01/02/1980).

To combine them, you might think to use this formula: =A2&” was born on “&B2

However, that’ll output: Marge Simpson was born on 29252. That’s because Excel converts the correctly formatted date of birth into a plain number.

By applying the TEXT function, you can tell Excel how you want the merged cell to be formatted. Like so: =A2&” was born on “&TEXT(B2,”dd/mm/yyyy”)

That’s slightly more complicated than the other formulas, so let’s break it down:

  • =A2 – merge cell A2.
  • &” was born on “ – add the text “was born on” with a space on both sides.
  • &TEXT – add something with the text function.
  • (B2,”dd/mm/yyyy”) – merge cell B2, and apply the format of dd/mm/yyyy to the contents of that field.

You can switch out the format for whatever the number requires. For example, $ #,##0.00 would show currency with a thousands separator and two decimals, # ?/? would turn a decimal into a fraction, H:MM AM/PM would show the time, and so on.

More examples and information can be found on the Microsoft Office TEXT function support page.

How to Remove the Formula From Combined Columns

excel combined cell with formula

If you click a cell within the combined column, you’ll notice that it still contains the formula (e.g. =A2&” “&B2) rather than the plain text (e.g. Marge Simpson).

This isn’t a bad thing. It means that whenever the original cells (e.g. A2 and B2) are updated, the combined cell will automatically update to reflect those changes.

However, it does mean that if you delete the original cells or columns then it will break your combined cells. As such, you might want to remove the formula from the combined column and make it plain text.

To do this, right click the header of the combined column to highlight it, then click Copy.

excel combined cell paste

Next, right click the header of the combined column again. This time, beneath Paste Options, select Values. Now the formula is gone and you’re left with plain text cells that you can edit directly.

How to Merge Columns in Excel

excel merge and center

Instead of combining columns, you can also merge them. This will turn multiple cells across into one cell. Merging cells only keeps the values from the upper-left cell and discards the rest.

To do this, select the cells or columns that you want to merge. In the Ribbon, on the Home tab, click the Merge & Center button (or use the dropdown arrow next to it).

For more information on this, read our article on how to merge and unmerge cells in Excel.

How to Save Time When Using Excel

Now you know how to combine columns in Excel, you can save yourself lots of time—you don’t need to combine them by hand.

If you want some more tips, here are more ways to save time in Microsoft Excel.

Read the full article: How to Combine Two Columns in Excel Easily and Quickly

MakeUseOf

  • Tweet
Tagged under: Columns, Combine, Easily, Excel, Quickly

About Malika Karoum

What you can read next

10 Amazing Google Chrome Experiments You Need to Try
Facebook’s Disturbing Friend Suggestions May Violate Your Privacy
Microsoft to Remove Handy Windows Feature in Upcoming Update

Malika Karoum Blog 2023

  • How to Delete the Last 15 Minutes of Your Google Search History

    There’s a quick way for you to clear your...
  • Lenovo Wants You to Know Its Yoga Pad Pro Can Be Used as a Portable Switch Display

    Sometimes, when playing with your Nintendo Swit...
  • The 5 Best Apps for Buying and Selling Pre-Owned Books

    We’ve all been at the point where we have...
  • Humble’s Recent "Heal Covid-19" Bundle Raised 1.2 Million for Charity

    To help raise money for COVID-19 relief in Indi...
  • Nintendo Partners With PlayVS to Make Its Games Recognized High School Varsity Athletics

    It’s odd—Nintendo gets a lot of flak for ...
  • The Pros and Cons of Playing Video Games on an Emulator

    If you’re a fan of playing retro video ga...
  • 5 Curators to Find the Best Articles Worth Reading on the Internet

    When anyone and everyone is a publisher, it isn...
  • Apple Could Unveil iPads With OLED Screens in 2023

    Apple only just switched from LCD to mini-LED d...
  • What Is Signal and How Does It Work?

    The chances are that you use at least one of th...
  • Samsung’s Upcoming Flagship Exynos Chipset Will Feature AMD’s RDNA2 GPU

    AMD confirmed its partnership with Samsung at C...
  • Atari Finally Reveals the Launch Date for the New Atari VCS Console

    At last, after what seems like an age (it pract...
  • Twitter Starts Testing Full-Screen Ads in Fleets

    Twitter has announced that it will be adding fu...
  • When Is Facebook Messenger Going to Offer End-to-End Encryption?

    Facebook Messenger is easy to use and has great...
  • Get Paid to Play Apps: How They Work and What You Risk

    You’ve probably seen advertisements for a...
  • When Will PS5 Production Ensure Supply Meets Demand?

    Despite the PS5’s launch taking place in ...
  • How to Manage Processes on Ubuntu Using System Monitor

    Linux, like most modern operating systems, is v...
  • How to Get Verified on Twitter and Finally Get That Blue Check Mark

    Twitter, like most social media platforms, offe...
  • 10 Street Photography Tips That Will Make You a Better Photographer

    Street photography is enjoyed by many enthusias...
  • Huawei Freebuds 4i Review: Quality ANC Earbuds for $100

    Huawei Freebuds 4i 8.00 / 10 Read Reviews Read ...
  • What Is Extended Reality (XR) and How Does It Work?

    We’re living in a digital age where the virtual...

MALIKA KAROUM ONLINE MARKETING PLATFORM

Office:
RME HOLDINGS SARL – DUBAI BRANCH

BUSINESS CENTER

Parcel ID: 345-835

Area: Bur Dubai

Sub Area: Burj Khalifa

UNITED ARAB EMIRATES

 

 

 

Malika Karoum Concept

Malika Karoum Projects

  • GET SOCIAL

© 2014 Malika Karoum -United Arab Emirate Dubai- All Rights Reserved

TOP