Excel may have a reputation for crunching numbers, but text-formatted data such as names, addresses, and descriptions is often encountered. Excel is also often used for processing data from a wide variety of unrelated databases, applications, and web sites.
With those, issues such as numbers formatted as text, text formatted as numbers, multiple text fields combined into a single cell, and broken tables are not uncommon. Further complicating things, Excel functions designed to manipulate text-formatted data are many, have inconsistent syntax, and often perform similar but slightly different tasks.
Still, you can perform almost every task with a small number of functions, and many things can be done by using Excel features.
The most powerful feature at your disposal is a common Find and Replace pop-up, which can be activated by pressing the Ctrl + F key combination:
Above, you can see a single table with several issues. Not only is the data not distributed properly into columns, but there are also several instances of multiple spaces between the fields instead of one.
Rather than using the TRIM function, you will probably resolve the issue with multiple unnecessary spaces faster if you just repeatedly replace two spaces with one space, i.e., by using Find and Replace.
We can also use the Text to Columns feature in order to properly format the table (note that only column A is selected):
If we pick the Delimited option in the first step, we can then set space and, for example, the at sign @ as delimiters:
Once we convert text strings to columns, you will notice that our table is still broken (as is often the case), and we will have to make some manual adjustments. We will insert some cells:
We will also delete some cells:
Basic text functions
Sometimes, we will want to combine multiple text inputs (cells, cell ranges, and/or text strings) into a single cell (single text string).
A new text string can be generated even without Excel functions, using only formulas.
Operator and & is used for this purpose, and possible additional text is nested inside of quotation marks “”.
Here is one example:
Note that every time we wanted to add a comma and space, we had to yet again use the operator and & and yet again insert that text inside quotation marks “”.
Specialized Excel functions such as TEXTJOIN and CONCAT can also be used to join text in a (sometimes) simpler way:
The EXACT function compares two text strings. If they are the same, the function will return TRUE, and if they are different, FALSE.
In our example table, we can check if our user registration process performed as expected – did it generate usernames that are the same as users e-mail addresses:
We’ve received two FALSE returns. In row 5, the user entered its e-mail address in uppercase, while the user registration process generated a lowercase username – this is not an issue. In row 6, the username is blank – that is an issue.
The LEN function returns the number of characters (including spaces) in a text string.
In our example table, we can check what number of characters some of our cells contain:
Returns here are actually numbers and can be further used in mathematical operations. If the LEN function encounters an empty cell, it will return 0.
The SEARCH function locates one text string within a second text string and returns its starting position.
If, for example, you want to locate the first occurrence of a single character inside a text string, you can search for that character.
The SEARCH function is not case-sensitive.
The FIND function works much the same way but is case-sensitive.
We can look for the character “a” in the first column:
If the SEARCH function encounters an empty cell or the searched-for character cannot be found, it will return an error.
The starting position can be defined for both the SEARCH and the FIND functions.
For example, formula =SEARCH(“a”;A7;2) would still look for the first occurrence of the character “a” in cell A7, but it would start looking at the second character and return 4 as a result.
LEFT, RIGHT and MID functions will return a defined number of characters from the left, right, or defined position in a text string:
The syntax of the functions is almost identical; we first define the referenced text string, follow with the starting position (if any), and finish with the number of characters we want to return.
Now consider the following example:
We are extracting second-level and top-level domains from column D. As the period character . separates second-level and top-level domain names, we can use the SEARCH function to locate its position in the text string.
Everything to the left of that character is second-level domain; we can then use the LEFT function to return it.
Everything to the right of that character is top-level domain; we can then use the RIGHT function to return it.
PROPER, UPPER, and LOWER are (fairly straightforward) functions that can be used to change the text case:
You can read more about how to perform text cleanup (removal of line breaks, multiple spaces, hyphenation, currency, letterlike symbols, etc.) in the article
Cleaning up text in Excel: CLEAN, TRIM, and SUBSTITUTE
How to convert number to text?
The ISTEXT function checks whether a value is text. The function returns TRUE or FALSE depending on the outcome of the check, i.e., it returns TRUE for text cells.
The ISNUMBER function checks whether a value is a number. The function returns TRUE or FALSE depending on the outcome of the check, i.e., it returns TRUE for number cells.
Both functions return FALSE for blank cells:
If we were to convert our cells from numbers to text, we would have little success by just formatting those cells as text.
If we right-click our selected cells and select Format Cells, we will open the Format Cells pop-up. Here, we can can select Text format instead of General:
This will make those numbers look like text, i.e., they will be aligned left.
However, as confirmed by ISTEXT and ISNUMBER returns, those are still numbers:
To actually convert those numbers, our first option is to add something called a leading apostrophe.
The apostrophe ‘ is a special sign in Excel when properly placed at the start of the value:
- it will not be displayed
- it will not be visible in Find and Replace
- it will be displayed in the formula bar and inside the cell after double-clicking
- everything inside a cell positioned right of the leading apostrophe will be treated as text, whether originally a number or a formula.
We can add a leading apostrophe by manually entering it left of the number, as shown below:
The leading apostrophe, even if not manually added, represents a solution with only specific uses.
It should also be noted that it can cause issues if the worksheet is expected to be uploaded to or opened with applications other than Excel.
A more general-purpose tool for conversions of numbers to text is the TEXT function. The TEXT function will perform two tasks:
- convert a number to text
- at the same time, format that number in a specified way.
We will show three examples here:
In column I, we are converting numbers from column F and formatting those numbers as plain text.
In column J, we are converting numbers from column F and additionally formatting those numbers.
In column K, we are converting numbers from column F and adding leading zeroes. This is useful when dealing with various identification numbers, which often have a fixed number of digits, and zero can be the starting one.
While the TEXT function is the most versatile option available for number-to-text conversions, it is also worth noting that many other text functions shown in this article will incidentally also convert numbers to text.
For example, you can convert the number 237 to text with both =LEFT(237;3) and =UPPER(237).
15 thoughts on “Text in Excel”