How to Parse and Combine Text with Formulas

FAQ — What does this article answer?

Q: How do I join the content of two cells into one?

A: Use the & operator to join cells together: For example =A1 & " " & B1 combines a first name and a last name with a space in between. For longer lists, TEXTJOIN is the better choice.

Q: How can I extract only part of a cell, such as the first three characters?

A: Use LEFT, RIGHT, or MID function. =LEFT(A1, 3) returns the first three characters, and =RIGHT(A1, 5) returns the last five. MID lets you start anywhere in the middle.

Q: What is the easiest way to split a cell containing a full name into first and last names?

A: In Excel 365 use, =TEXTBEFORE(A1, " ") for the first name and =TEXTAFTER(A1, " ") for the last name. In older versions of Excel, use a combination LEFT or RIGHT and FIND to locate the space and extract the text around it.

Q: My imported data has extra spaces and mixed capitals. How can I clean it up?

A: Use the formula =TRIM(A1) to remove extra spaces. =PROPER(A1) capitalises the first letter of every word. Nest them: =PROPER(TRIM(A1)) for a clean name in one formula.

Q: How can I incorporate a number or date into a sentence?

A: First, use the TEXT() function to convert it first: ="Report for " & TEXT(TODAY(), "mmmm yyyy") produces "Report for May 2026". Without the TEXT() function, Excel would paste the raw serial number into the sentence.

Why text formulas matter

Spreadsheets are not just for numbers. A large proportion of real-world data is text-based, such as names, product codes, addresses, status labels, file paths and invoice numbers. When this data is imported from other systems, such as exported databases, CSV files or copied web tables, it is almost never in the exact format you need. Text formulas allow you to reshape the data without altering the original and without doing the work manually.

This article covers everything you need to know, from the simplest way to join two cells to the modern splitting functions introduced in Excel 365.

Joining text — three ways

The ampersand (&) operator

The ampersand & is the quickest tool for joining text. Place it between any two values, such as cell references, text in quotation marks or formula results.

=A1 & " " & B1

If A1 contains "Anna" and B1 contains "Müller", the result is "Anna Müller". The " " in the middle adds a space; without it, you would get "AnnaMüller".

You can use as many & (ampersand) as you like and mix cell references with fixed text freely:

="Invoice #" & C1 & " — " & D1 & " EUR"

This might producethe following: "Invoice #1042 — 3.400 EUR"

CONCAT: Join a range in one step

CONCAT performs the same function as the ampersand (&), but accepts a range of cells, saving you from having to write out every cell individually.

=CONCAT(A1:A5)

This joins all five cells in column A, one after the other, with no separator. This is useful for building codes or reference strings from parts stored in separate columns:

=CONCAT(A1, "-", B1, "-", C1)

Please note that CONCAT does not automatically add a separator. If you need a delimiter between every item, use TEXTJOIN instead.

TEXTJOIN is the best option for lists

TEXTJOIN adds a separator between every item and can automatically skip empty cells, making it the most practical function for joining variable-length lists.

=TEXTJOIN(", ", TRUE, A1:A10)

• The first argument is the separator, which is a comma and space here.
• Second argument: TRUE to skip empty cells (almost always what you want).
• Third argument: the range or individual values to join.

If five of the ten cells are empty, TEXTJOIN silently skips them, giving you a clean result with no double commas.

Extracting parts of a cell

When you receive data such as product codes, IDs or names in one field or addresses, you need to extract specific parts. These three functions can help you do that!

LEFT: Take characters from the beginning

=LEFT(A1, 3)

It returns the first three characters. For example, in "INV-2026-00147", this would give you "INV". Use this function whenever the part you want always starts at position 1 and has a fixed length.

RIGHT: Take characters from the end

=RIGHT(A1, 5)

It returns the last five characters. For example, in "INV-2026-00147", it would return "00147". Use this function for suffixes, postcodes, or any value that always appears at the end.

MID: Take characters from anywhere

=MID(A1, start, length)

The MID function lets you start at any position. In "INV-2026-00147", for example, the year starts at position 5 and is four characters long.

=MID(A1, 5, 4)   →   "2026"

💡 Tip: Positions are counted from the left, starting at 1. The first character in a cell is always in position 1.

FIND and SEARCH: Locate a character

If the text you want is not always in the same position, you need to find it dynamically. The FIND function returns the position number of a character or substring.

=FIND("-", A1)   →   4

For example, this tells you the dash is at position 4. You can then use this number with the LEFT, RIGHT, or MID function:

=LEFT(A1, FIND("-", A1) - 1)

This extracts everything before the first dash, regardless of how long the preceding text is. FIND is case-sensitive. Use SEARCH if you want case-insensitive matching. SEARCH also supports the wildcards * (any number of characters) and ? (any single character).

The modern way to split — Excel 365

If you use Excel 365, three new functions replace the need for juggling with the LEFT/FIND functions, making things far more readable.

TEXTBEFORE: Everything left of a delimiter

=TEXTBEFORE(A1, " | ")

It returns everything to the left of the first occurrence of " | ". An optional third argument allows you to target the second, third or subsequent occurrences.

TEXTAFTER: Everything right of a delimiter

=TEXTAFTER(A1, " | ")

It returns everything to the right. Use a negative instance number to count from the end:

=TEXTAFTER(A1, " | ", -1)

This returns everything after the last pipe, regardless of how many there are. This is very handy for extracting the final segment of a path or code.

TEXTSPLIT: Split text into multiple cells

TEXTSPLIT is the most powerful of the three functions. It splits a string and displays the results in neighbouring cells, without the need for copying or helper columns.

=TEXTSPLIT(A1, ", ")

If A1 contains "Berlin, Sales, Q2, 2026", this produces four separate cells: Berlin | Sales | Q2 | 2026.

You can even split in both directions at once. This can be done horizontally by one delimiter and vertically by another. This produces a two-dimensional result from a single formula.

Excel version note: TEXTBEFORE, TEXTAFTER, and TEXTSPLIT require Excel 365. For Excel 2019 and earlier versions, use the combination of LEFT, RIGHT and MID with FIND instead.

Text cleaning and conversion

Imported data is almost never clean. These functions solve the most common issues.

Spaces: TRIM

=TRIM(A1) removes all leading and trailing spaces and replaces sequences of multiple spaces with a single space. This fixes the most common import issue in one step.

Capitalisation — UPPER, LOWER, PROPER

• =UPPER(A1): converts everything to capitals
• =LOWER(A1): converts everything to lowercase
• =PROPER(A1): capitalises the first letter of each word

To clean imported names, nest TRIM inside PROPER:

=PROPER(TRIM(A1))

Replacing the text: SUBSTITUTE

SUBSTITUTE replaces every instance of one piece of text with another:

=SUBSTITUTE(A1, "-", " ")

This replaces every dash with a space. Unlike REPLACE (which works by position), SUBSTITUTE works by matching text, so you do not need to know where in the cell the text appears.

Counting characters: LEN

=LEN(A1) returns the total number of characters in a cell, including spaces. This is useful for validation, such as checking that a product code is exactly 8 characters, and for dynamic MID formulas where you need the length.

Converting numbers: VALUE and TEXT

These two functions are opposites of each other.

VALUE converts a text string that looks like a number into an actual number that Excel can perform calculations with:

=VALUE("1.250")   →   1250

The TEXT function converts a number or date into a text in the specified format:

=TEXT(TODAY(), "dd/mm/yyyy")   →   "12/05/2026"

Use TEXT when you want to embed a number or date in a sentence created using & or TEXTJOIN. The result is text, so you cannot use it in a SUM or other calculation.

A practical example: creating a clean name and email address

Suppose your import file has three columns: a raw first name in column A (with mixed capitalisation and extra spaces), a last name in column B, and a domain in column C. You want the first name to be displayed properly and the email address to be in lowercase.

Display name:

=PROPER(TRIM(A2)) & " " & PROPER(TRIM(B2))

Result: "Anna Müller"

Email address:

=LOWER(TRIM(A2)) & "." & LOWER(TRIM(B2)) & "@" & C2

Result: "anna.müller@company.com"

Both formulas update automatically when the source data changes. There is no need for manual editing or copy-pasting. This is the core idea behind text formulas: describe the transformation once and let Excel apply it to every row.

Summary: which function for which task?

Start with the functions &, LEFT, RIGHT, and TRIM. These four cover the majority of everyday text work. Once you are comfortable with those, the combination of MID and FIND, as well as the modern TEXTSPLIT family, will enable you to do everything else.