In a previous article I wrote about formatting numbers, here we will discuss what some of the basics of what we can do with text. Unlike numbers, text itself does not have formatting options, e.g. you cannot a format text that it only displays the first three characters of a the text without performing an operation on the text. However we can still do much with text to help us work with data and make better visuals
In this article we will look at some basic attributes of text and some functions to use on text:
- Ordered Collection of characters
- T and Trim
Oredered collection of characters
Text is essentially an ordered collection of characters, each character has a value (what the actual character is) and position. Thinking of text this way we can starting seeing options how to manipulate it. As the example above maybe we only want three characters. For example you can use the first 3 characters of a Canadian postal code to define very specific regions. Maybe you are trying to group up sales in Vancouver and want to see how each area is doing. So we can create another column for the sales which has just the first three characters. Or if we have a column with first name and another with last name we can join the two together to make one column which I’ve used in another article before when needed for vlookups.
As I’ve discussed before, the CONCATENATE function allows you to join text from several cells together:
Of course it doesn’t have to be just two, you can add as many cells as you want, separated by commas). Note however that in the image above there is no space between the first and last names, that is because the function literally slaps together the two cells’ values, so unless there is a space in one of the cells you will not get one as a result. To remedy this you need to add a space your self, to do this you need to add the space in quotes. In fact you can add any text you like with quotes around it. Say you want the Full name to be of the format ‘Last Name, First Name’ then you can do this:
Note I reversed the order of the cells, with D4 being the first one, then I put in “, “. The quotes tell Excel that what is in between them is text value (versus a number, or a cell reference for example), also note that space after the comma, that is there to add a space before the First name.
You can use many things besides text and cells with text with CONCATENATE. You can use numbers, either explicitly – as one of the parameters – like with text but no quotes; or implicitly by referring to a cell. Either way Excel will convert the number in to text and add it to the other parameters. You can even do calculations as a parameter or put in functions such as IF statements or aggregations such as sums. As long as the output is a format that can be converted to text like numbers, or boolean (TRUE/FALSE)
Concatenate is great to build up complex text, and can event automatic some commentary when it is very static (e.g. Sales changed month over month by x%)
As I mentioned above text to excel is merely collection of characters is specific order. One of the more common attributes of text is the number of those characters, aka length. To get the length of text in a cell simply use the function LEN:
Note that Excel counted the spaces as well, as a space is also a character.
Since whenever Excel tries to do text operations on a number it will convert the number to text, then this it will count the characters, so each digit is its own character – e.g. LEN (28) = 2
Length is useful in many situations, one example is if we want to catch errors. Say we have someone entering student id’s manually and we check for errors. One of the checks we can do is that the length of the id is what it should be. Another common use is with combination of RIGHT function and LEN, which will be covered in the next articles on working with text.
T and TRIM
As mentioned above when excel expects text but gets a number it will often convert it to text. What if we wanted to do some operation only on text, i.e. ignore any numbers. T comes in useful for that as it checks whether the cell’s values are already text and if so returns the text, otherwise you get empty text “” (text with zero characters)
I use this one often when there is a column that has different numbers but only the text ones matter (e.g. numerical id’s are all automated, while text id’s are human)
Similarly in the family of pre-processing your text is the TRIM function which helps removing unnecessary spaces. Say someone copied from from a source which introduced an extra space at the front, or there is too many spaces between words. Simply apply TRIM and it will remove the extra spaces:
Note how it removed both the leading spaces, the trailing spaces as well as extra spaces between words except single space
One of the more complicated but more useful operations is Find. You can give it text that it will try to find in a cell, and if found will return to you the position of that character. Here is a simple example for text that has a dash in it (for example City-Province). We want to find which character the dash is:
As you see above there is an optional parameter to the function – the starting number. Say you want to find the text but from the 10th character on, you simply put a 10 there. We can take advantage of this by nesting FIND functions to get the second dash. Going back to the example above, let’s augment it with country following province like so: City-Province-Country:
These methods become really useful when trying to breakdown text into parts. Note the +1 after the second find. This is so that it starts after the first value, otherwise it will return the location of the first ‘-‘
Another really useful option of FIND is to text match. You don’t have to do just one character, you can put in any text to find, so you can use that to find which cells have certain text you want. For example if we wanted to find anyone in Canada:
Excel tries to find the text Canada, and if it does it shows you the where the first letter is, if it doesn’t it presents an error. This way we can quickly identify only the records we want
Putting it all together
Okay so by now hopefully you gained some understanding on how to work with text. You should know how Excel treats text, how to join two or more text values together and how to find the length of the text. You can clean up extra spaces on text as well as find a particular character’s location in text We also learned some text identifying techniques using T function and looking for specific text using FIND.
Next time we will cover some more advanced techniques building on some of what we learned here such as splitting up text and getting specific parts