Excel Text Functions
There are a total of 24 functions that are categorized as excel text functions. Though we are all sure that all of them are very valuable and all, some of them gets to be used on daily basis. Lets have a look at most used and useful excel text functions.
For example formulas, we are going to use this data:
LEFT() : Returns a specified number of characters form a text value starting from left.
=LEFT(A1;3) will result as “bea”
RIGHT() : Returns a specified number of characters from a text value starting from right.
=RIGHT(A1;3) will result as “eat”
MID() : Returns a specified number of characters form a text value starting from specified starting position.
=MID(A1;2;2) will result as “ea”
CONCATENATE() : Joins several text values into one.
=CONCATENATE(A1;B1) will result as “beatexcel”
An alternative way of joining text is using “&” as an operator. It is much easier and faster in my opinion. For more information on this, check Joining Text in Excel.
TRIM() : Removes blank spaces from text values. For several reasons your text values may have blank spaces in front or at the end. While they are not visible to eye and seem harmless, they do not match with non-spaced versions of the same text. Trim function removes these blank spaces and gives you only the non-blanks.
=TRIM(“ beatexcel ”) will result as “beatexcel”
VALUE() : Returns number value of a number stored as text. If for some reason you come across numbers that are stored as text, they will not be usable as numbers in equations. To be able to use them as numbers, you need to use VALUE() function to convert them to numbers.
LEN() : Returns number of characters in a text value.
=LEN(A1) will result as 4
This is useful when you have a bunch of text values with different number of characters and you need to (for example) change all characters starting from 2nd. Here is how to select them:
For A1=”beat” =RIGHT(A1;(LEN(A1)-1)) will result as “eat”
For A1= “computer” =RIGHT(A1;(LEN(A1)-1)) will result as “omputer“