98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
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:
=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.
Author is a freelance copywriter. For more information on application development excel, please visit https://www.stl-training.co.uk
Original article appears here:
https://www.stl-training.co.uk/article-1237-some-examples-excel-text-formulas.html
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsMathys & Squire LLP
Senior Record Clerk Kiran Khella Effective Communication Skills Tony B was amazing and had the whole group engaged and interacting - thank you Seven
Assistant Project Manager William Dowling Managing Upwards Tony was great, he made me feel comfortable to contribute and I thoroughly enjoyed the experience - thanks tony! Casio Electronics Company Ltd
Divisional Head Operational Services Division Richard Sharpe Excel Intermediate Everything was excellent with the course. The trainer Sarah was excellent, knowledgeable, patient and helpful. Explanations were clear and concise!! |
PUBLICATION GUIDELINES