Excel to Python: CONCAT Function - A Complete Guide | Mito
Home Icon






How to Use Excel's CONCAT Function in Pandas

Excel's CONCAT function combines two or more strings into one. It's a powerful tool in text-based data analysis, and is often used to combine fields or create unique identifiers.

This page explains how to implement Excel's CONCAT function in Python using pandas.

Mito is an open source library that lets you write Excel formulas in Python. Either write the formula directly in Python or use the CONCAT formula in the Mito Spreadsheet and generate the equivalent Python code automatically.

Mito's CONCAT function works exactly like it does in Excel. That means you don't need worry about managing data types, handling errors, or the edge case differences between Excel and Python formulas.

Install Mito to start using Excel formulas in Python.

# Import the mitosheet Excel functions
from mitosheet.public.v3 import *;

# Use Mito's CONCAT function
df['Combined'] = CONCAT(df['StringCol'], df['NumberCol'])

To replicate the CONCAT function in Excel using Python and pandas, there are several methods depending on the specific need. Here are some common implementations:

Combining values in a row is similar to entering =CONCAT(A1, B1) in Excel.

The following code shows how to concatenate values in columns 'Col1' and 'Col2' into a new column 'Combined':

df['Combined'] = df['Col1'] + df['Col2']

To introduce a separator, like a space or a hyphen, between concatenated values in Excel, you would use =CONCAT(A1, "-", B1).

Similarly in pandas, you'd add the separator string in between columns:

df['Combined'] = df['Col1'] + '-' + df['Col2']

If you need to concatenate numbers with strings, it's essential to convert numbers to string format first.

Excel takes care of data type handling for you, so you could write the formula =CONCAT(A1, B1) where A1 is text and B1 is a number without any problems. However, in Pandas, you need to explicitly convert the number to a string before concatenating:

df['Combined'] = df['StringCol'] + df['NumberCol'].astype(str)

There might be scenarios where you want to concatenate only if a certain condition is met.

For example, in Excel: =IF(C1>10, CONCAT(A1, B1), A1). In pandas, you can use the `.loc` to conditionally select columns that meet a criteria:

df.loc[df['C'] > 10, 'Combined'] = df['A'] + df['B']
df.loc[df['C'] <= 10, 'Combined'] = df['A']

While using pandas for concatenation, there are certain pitfalls that might be encountered. Here are some common mistakes and their resolutions.

Excel formulas handle data types dynamically, so you could write the formula =CONCAT(A1, B1) where A1 is text and B1 is a number without any problems. However, in Pandas, attempting to concatenate columns with different data types raises a TypeError.

You should explicitly convert any non-string columns to strings before concatenating:

df['Combined'] = df['Col1'].astype(str) + df['Col2'].astype(str)

Again, Excel takes care of missing values for you, but in pandas, you need to explicitly handle them.

If any of the values in the columns being concatenated are NaN, the result will be NaN. To avoid this, use the `fillna()` method to replace NaN values with empty strings.

df['Combined'] = df['Col1'].fillna('') + df['Col2'].fillna('')

The CONCAT function in Excel takes two or more string arguments and combines them into one continuous string.

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

CONCAT Excel Syntax

ParameterDescriptionData Type
text1The first text value you want to concatenate.string
text2(Optional) Additional text values to concatenate.string
...(Optional) Add up to 255 text values.string


=CONCAT("Hello", " ", "World")Combine the words 'Hello' and 'World' with a space in between.Hello World
=CONCAT(A1, "-", B1)Combine the words in A1 and B1 with a - in between.value_of_A1-value_of_B1

Don't re-invent the wheel. Use Excel formulas in Python.

Install Mito

Don't want to re-implement Excel's functionality in Python?

Automate analysis with Mito