Some Examples Of Excel Text Formulas

Excel is designed to work with a range of formats including numbers, dates, times and text. There are therefore many formulas which specifically enable the user to analyse and manipulate text type data. This article looks at some of those formulas and explains how to use them. This is particularly useful when confronted with an output of data from another system that needs to be formatted in order to make it useable for your purposes.


Changing the Case of the Text
If you have text that needs to be converted to lowercase from uppercase or vice versa then there are two handy formulas which allow this as follows:

=Lower()

=Upper()

In the brackets should be the cell that needs converting, for example, =Lower(A3). The formula can be dragged down to automatically convert a list. All text in the cell will be converted to the new requested case. The only problem with this formula is that if, for example, you have a name in a cell then you may want the first letter of each word to be in uppercase and the rest of the letters in lowercase. The formula to achieve this is as follows:

=PROPER(A1)


Joining Text Together
Sometimes there are several different words all in their own column and you need to merge them into one cell. This can be done by use of the following formula:

= CONCATENATE()

Say for example you would like to merge cell A1 and cell B1 with a space in the middle, for example a first name, a space and a surname. To do this, use the following formula:

=CONCATENATE(A1," ",B1)

As you can see, cells can simply be added, while any spaces or additional text can be placed in quotation marks. Up to 30 different things can be concatenated, but if you run out of room then you can concatenate up to the maximum in one field and then complete the remaining in another field and concatenate the two results together.


Checking whether two values are the same
If there are two columns with similar data, it may be that you need to compare them to find either which ones are the same or which ones are different. Do this with the Exact formula as follows:

=EXACT(A1,B1)

If the two cells are the same then the result will be returned as 'TRUE' and if the result is not the same then the result will be returned as 'FALSE.' This can be used on mixed data types.


Changing Text Values To Number Values
It can be problematic when dealing with an output of data that contains values entered in the sheet as text. It may be that you could highlight the column and format all the values as numeric, but on occasion this does not work. To ensure that everything is converted correctly, use the converting formula to convert the text to numbers correctly as follows:

=VALUE()

Simply placing the cell number in the brackets will allow Excel to complete the conversion. This will work when converting values that appear to be values, but will also work on date fields. This is because Excel actually stores dates as numbers.


Returning a specific number of characters in a string
I have often had lists of data that contain reference numbers or ID fields which are similar to my own, but not quite right. In this case I have often found it useful to cut a certain number of characters from the left or the right of the string using the following formula:


=RIGHT(Text, Num_Chars)
The text part is asking you to specify the cell that needs to be cut and the Num_Chars part is asking you to specify how many characters you would like to return in the resulting cell. The following example takes the value in cell A1 and cuts the last 3 cells from the value, placing them in the results cell:

=RIGHT(A1,3)

So if you had a value in cell A1 of 'March_001' then the result would be '001' in the results field. A similar formula can be used to return from the left as follows:

=LEFT(Text, Num_Chars)

There are many more formulas based on text fields within Excel and many different ways in which they are used. If you are interested in learning more then perhaps a professional training course may be useful as a starting point.