excel

How To Combine Two Columns In Excel With A Dash

Jump to

    Merging two columns in Excel with a dash is a common data manipulation task, often required for combining data fields like first and last names, or city and state. This process can be achieved using various Excel functions and formulas.

    While Excel offers native solutions for this task, users may encounter complexity and limitations. In this guide, we'll provide clear instructions on how to combine two columns with a dash in Excel and discuss why using Sourcetable can simplify this process.

    Combining Two Columns in Excel with a Dash

    Using the CONCATENATE Function

    Excel's CONCATENATE function can merge two columns of numbers with a hyphen without altering the original values. To combine columns A and B with a dash, use the formula =CONCATENATE(A1, "-", B1) in a new column. This approach maintains any leading zeros when using the TEXT function.

    Employing the Ampersand (&) Operator

    The ampersand symbol (&) offers a quick alternative for concatenating values. Apply the formula =A1 & "-" & B1 to merge sports scores from columns A and B with a hyphen in between, ensuring the scores are represented exactly as they appear.

    Utilizing the CONCAT Function

    The CONCAT function in Excel efficiently combines multiple cells into one and can include additional characters like dashes. Use =CONCAT(A1, "-", B1) for a straightforward merge. The CONCAT function can replace CONCATENATE and works similarly to TEXTJOIN but without a delimiter argument.

    Preserving Leading Zeros

    To keep leading zeros in scores when combining columns, integrate the TEXT function within your formula, such as =CONCATENATE(TEXT(A1, "00"), "-", TEXT(B1, "00")) or =A1 & "-" & TEXT(B1, "00") depending on whether you are using CONCATENATE or the ampersand operator.

    Common Use Cases

    • excel

      Creating unique identifiers from first and last names

    • excel

      Combining date and time data for timestamp analysis

    • excel

      Merging product codes with color codes for inventory tracking

    • excel

      Joining city and state names for location reporting

    • excel

      Combining genetic loci information in bioinformatics

    Excel vs. Sourcetable: A Modern Data Management Comparison

    Excel, a long-standing tool in data management and analysis, meets its innovative match in Sourcetable. Sourcetable's unique ability to amalgamate data from various sources into a single interface streamlines the data query process, a feature Excel lacks.

    Unlike Excel, Sourcetable's integration of an AI copilot transforms user experience. This AI assistance facilitates the creation of formulas and templates, catering to users seeking a more intuitive and efficient workflow.

    Sourcetable stands out with its chat interface, which simplifies complex tasks. This contrasts with Excel's traditional formula-driven approach, which often requires a steeper learning curve and specialized knowledge.

    Recommended Reading

    • How to... how to add a dash in excel
    • How to... how to insert dash in excel between text
    • How to... how to add a dash between numbers in excel
    • How to... how to combine columns in excel with a space
    • How to... how to remove dash in excel
    • How to... how to combine three columns in excel
    • How to... how to add hyphen in excel


    Sourcetable Logo

    Try Sourcetable To Get Insights From Your Data

    Ask questions about your data in a powerful spreadsheet that your whole team knows how to use.

    Drop CSV