In Excel, we work mostly with numbers (aggregation via SUM, AVERAGE, etc.) or with dates. However, it is true that very often we need to adjust, combine or clean text columns. There are many situations where we want to edit the text in some way (it cannot be generalized). It depends on a case-by-case, but the usage examples below will help. Usually, this need arises when exporting data from a certain system where values are presented, for example, with some prefixes, etc.

There are about 30 functions in Excel that are directly designed to work with text. Some of them are used more or less. In this article, you will find a list of the most used ones with examples. If you are interested in an overview of the most frequently used functions in Excel and various points of interest, read the article Excel | Overview of frequently asked questions and answers about Excel functions

Combining Text Strings – CONCAT (), TEXTJOIN () Functions or Operator &

Combining text fields is probably the most common task. There are basically 2 ways we can combine text:

• CONCAT () to join multiple texts or
• Use the & operator

Combine strings syntax

= CONCAT (range) or CONCAT (cell 1; cell 2; cell 3, …, cell n) – in the function we refer to cells or range
= CONCAT (“Text 1”; “Text 2”, “Text 3”, …, “Text n”) – if you type manually written string into the function, you must put it in quotation marks
= For & operator, the syntax is similar, ie = Cell 1 & Cell 2 & Cell 3, …, Cell n and alternatively for text.

Tip: The obvious advantage of CONCAT over & is the fact that we can refer to a range in a function.

Function TEXTJOIN allows to combine text more efficiently

In some situations, it is preferable to use the TEXTJOIN () function. It does the same thing as CONCAT, but it has one major advantage. With CONCAT function, we cannot choose a custom word separator.

Syntax: TEXTJOIN (<separator>; <ignore empty cells (true, false)>; <text>)

More about CONCAT and TEXTJOIN in article – CONCATENATE, TEXTJOIN | Excel – Combine text strings and cells

Replacing Value in Text – SUBSTITUTE () Function

Another useful skill is to be able to replace a value in the text using a formula and substitute another value instead. To do this, use the SUBSTITUTE function.

Replacing using SUBSTITUTE – Syntax

=SUBSTITUTE(<cell with text>; <value to replace>; <new value>)

Let’s say we want to replace the text “Hello” to “Hi” in the previous example before combining all words.

Text Length (characters) – LEN () Function

The number of characters in a text string can be measured using the LEN () function. The function returns the length of the text string in terms of the number of characters.

For example, LEN (“Hello”) returns 5. If there are spaces at the end of the text, the function counts them as well.

Text length syntax

= LEN (<cell with text>)

Note: In this case, the function returns 1 extra character because there are spaces in the text.

Extract Part of Text – LEFT (), RIGHT (), MID ()

Another situation is that we need to select a certain number of characters from the text, such as left, right, or somewhere inside the text.

The syntax for the extraction by using the LEFT() function:

= LEFT (<cell with text>; <number of characters we want to select>)

Note: If the part of the text we need to extract is somewhere inside another text, we use the MID() function

Remove Text Spaces – CLEAN () Function

Sometimes we work with text values that contain extra spaces at the beginning, at the end, or between words. You can use the CLEAN function to clear these extra spaces. CLEAN function removes spaces as follows:

• All spaces from left (before text)
• All spaces from right (after text)
• If the text contains more words with extra spaces, function leaves one space between each word

On the screenshot, we see the text “Hi, I am the text” and I put a lot of spaces in the text. Then I applied the function CLEAN and most rows ended successfully, but row nr 13 is wrong. That’s because the function always leaves one space between words or characters.