99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Some Examples Of Excel Text Formulas
Wed 27th October 2010
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:
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:
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:
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:
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:
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:
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:
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:
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:
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.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
UK Audiences Coordinator
Lily Van Den Broecke
Thanks for today - it was very helpful. I have rated everything very highly since it was of great quality. I can imagine it's very hard to cater to everyone's needs but I think Linda did that well. I think I will aim to come back for the next level of training.
I found this course extremely useful. It matched the brief and I found the trainer touched subjects that we requested.
Junior Product Manager
Challenged to help Imbed learnt skills
answered question efficiently
Trainer very knowledgeable