일. 8월 17th, 2025

Have you ever found yourself with bits of text scattered across multiple cells in Excel and wished there was a magic wand to combine them seamlessly? 🤔 You’re in luck! Excel offers powerful functions to merge text strings, making your data clean, organized, and ready for analysis. Today, we’re diving deep into two essential functions: CONCATENATE and TEXTJOIN. Let’s unlock the secrets to mastering Excel text merging! 🚀


Why Merge Text in Excel?

Merging text is a common task in data management. Imagine you have a first name in one column, a last name in another, and you need a full name. Or perhaps a street address, city, and zip code spread across different cells, and you need a complete address for a mailing list. This is where CONCATENATE and TEXTJOIN come in handy! They eliminate manual copy-pasting and make your data manipulation efficient. ✨


1. CONCATENATE: The Classic Choice 🕰️

CONCATENATE is one of Excel’s oldest and most straightforward functions for combining text. It’s like bringing individual words together to form a sentence.

What it Does:

It joins two or more text strings into one string.

Syntax:

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

  • text1: The first item to join. This can be text, a number, or a cell reference.
  • [text2], …: Additional items to join. You can add up to 255 items!

How to Use It:

Let’s say you have “John” in cell A2 and “Doe” in cell B2.

Example 1: Basic Name Combination Goal: Combine First Name and Last Name.

  1. Input Data:
    • A2: John
    • B2: Doe
  2. Formula in C2: =CONCATENATE(A2, B2)
  3. Result: “JohnDoe”

Wait, that’s not quite right! We need a space. 🚀

Example 2: Adding a Space To add a space between “John” and “Doe”, you need to include a space as a separate text argument.

  1. Formula in C2: =CONCATENATE(A2, " ", B2)
    • Here, " " is a text string representing a single space.
  2. Result: “John Doe” 🎉

Example 3: Combining with Punctuation and Custom Text Let’s add a title and a job role.

  • A2: Ms.
  • B2: Jane
  • C2: Smith
  • D2: Manager
  1. Formula: =CONCATENATE(A2, " ", B2, " ", C2, " - ", D2)
  2. Result: “Ms. Jane Smith – Manager”

Pros & Cons of CONCATENATE:

  • Pros:
    • Simple to understand and use for a few items.
    • Widely compatible with older Excel versions.
  • Cons:
    • Can become very long and tedious if you have many cells to combine, as you have to list each cell reference and delimiter individually. Imagine combining 20 cells! 😫
    • Doesn’t automatically handle empty cells or delimiters. You have to manually add spaces or punctuation.

2. TEXTJOIN: The Modern Powerhouse 🏗️

Introduced in Excel 2016, TEXTJOIN is a game-changer! It addresses the limitations of CONCATENATE by simplifying the process of combining text from a range of cells and offering more control over delimiters and empty cells.

What it Does:

It joins text strings from a range, array, or multiple items, using a specified delimiter between each item. It also allows you to choose whether to ignore empty cells.

Syntax:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

  • delimiter: The text string to place between each combined item. This can be a space, comma, dash, etc., enclosed in double quotes (e.g., “, “, “-“).
  • ignore_empty: A logical value (TRUE or FALSE) that specifies whether to ignore empty cells.
    • TRUE: Ignores empty cells. (Recommended! 👍)
    • FALSE: Includes empty cells (resulting in extra delimiters).
  • text1, [text2], ...: The text items or cell ranges to join. This is where TEXTJOIN shines, as you can specify a range like A2:A10 instead of A2, A3, A4, ....

How to Use It:

Let’s use the same names: “John” in A2, “Doe” in B2.

Example 1: Basic Name Combination with Delimiter Goal: Combine First Name and Last Name with a space.

  1. Input Data:
    • A2: John
    • B2: Doe
  2. Formula in C2: =TEXTJOIN(" ", TRUE, A2, B2)
    • " " is our delimiter (a space).
    • TRUE means ignore any empty cells.
  3. Result: “John Doe” 🎉

Example 2: Combining a Range of Cells with Commas Imagine you have a list of ingredients in cells A2 to A5:

  • A2: Flour
  • A3: Sugar
  • A4: Eggs
  • A5: Milk

Goal: Combine them into a single cell, separated by a comma and a space.

  1. Formula: =TEXTJOIN(", ", TRUE, A2:A5)
    • ", " is our delimiter.
    • TRUE ensures if one ingredient cell is empty, it won’t add an extra “, “.
  2. Result: “Flour, Sugar, Eggs, Milk” 🍳

Example 3: Handling Empty Cells Gracefully Let’s say you have address parts:

  • A2: 123 Main St
  • B2: Apt 4B
  • C2: (empty cell)
  • D2: Anytown
  • E2: USA

Goal: Combine them into a full address, separated by commas, but without extra commas for empty cells.

  1. Formula: =TEXTJOIN(", ", TRUE, A2:E2)
  2. Result: “123 Main St, Apt 4B, Anytown, USA”
    • Notice how the empty C2 cell was completely ignored! If you used FALSE for ignore_empty, you’d get “123 Main St, Apt 4B, , Anytown, USA” which looks messy.

Pros & Cons of TEXTJOIN:

  • Pros:
    • Handles ranges: Can combine cells from a continuous range (e.g., A2:A100) with ease. This is its biggest advantage!
    • Delimiter argument: Specifies a common delimiter for all items, saving you from typing it repeatedly.
    • ignore_empty argument: Automatically skips blank cells, preventing extra delimiters and messy outputs.
    • Much more efficient and cleaner for combining many cells.
  • Cons:
    • Not available in Excel versions older than 2016. If you’re sharing files with users on older versions, they might not be able to use it.

CONCATENATE vs. TEXTJOIN: A Side-by-Side Comparison 🥊

Here’s a quick overview to help you decide which function to use:

Feature CONCATENATE TEXTJOIN
Availability All Excel versions Excel 2016 and later
Input Individual cell references or text strings Individual references/text OR ranges (e.g., A1:A5)
Delimiter Handling Must be added manually for each text item (" ") Dedicated delimiter argument, applied automatically
Empty Cells Must be manually managed (can lead to extra spaces/delimiters) ignore_empty argument (TRUE/FALSE) for automatic handling
Complexity for Many Items High (long formulas with repeated arguments) Low (simple range input)
Best Use Case Combining 2-3 specific text items Combining multiple items from a range, especially with delimiters or varying data

When to Use Which:

  • Use CONCATENATE (or the & operator, which is often simpler for simple cases):

    • When you only need to combine a few specific cells (2-3 items).
    • When you are working with older versions of Excel.
    • When you need highly specific, non-uniform delimiters between items that can’t be handled by a single TEXTJOIN delimiter.
  • Use TEXTJOIN (whenever available):

    • When you need to combine a range of cells.
    • When you want a consistent delimiter between all items (e.g., comma, space, dash).
    • When you want to automatically ignore empty cells in your range.
    • When you want to write a cleaner, more efficient formula.

Advanced Tips & Tricks 💡

  1. Adding Line Breaks: Use CHAR(10) as your delimiter in TEXTJOIN to put each item on a new line within the same cell. Remember to enable “Wrap Text” for the cell!

    • Example: =TEXTJOIN(CHAR(10), TRUE, A2:A5) will list each ingredient on a new line.
  2. Using & Operator: For simple concatenations, many users prefer the & (ampersand) operator, which acts like CONCATENATE but is often quicker to type.

    • Example: =A2 & " " & B2 gives “John Doe”.
  3. The CONCAT Function (Newer CONCATENATE): Excel also introduced the CONCAT function (Excel 2019+), which is an improvement over CONCATENATE because it does allow you to select a range! However, it doesn’t have the delimiter or ignore_empty arguments like TEXTJOIN. So, while CONCAT is better than CONCATENATE for ranges, TEXTJOIN remains supreme for control over delimiters and empty cells.

  4. Combining with Other Functions: You can nest these functions with others for more power. For instance, TRIM can remove extra spaces, UPPER can capitalize, etc.

    • Example: =CONCATENATE(UPPER(A2), " ", B2) to ensure the first name is capitalized.

Conclusion ✨

Mastering CONCATENATE and TEXTJOIN will significantly boost your Excel productivity. While CONCATENATE is a reliable classic for basic needs, TEXTJOIN is the modern, powerful solution for complex text merging scenarios, especially when dealing with ranges and needing precise control over delimiters and empty cells. Practice these functions with your own data, and you’ll be merging text like a pro in no time! Happy Excelling! 📊🚀 G

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다