While working with Excel, there are a few important functions that everyone needs to know. LEFT, RIGHT and CONCATENATE text functions are amongst these. Let us look at each one of them in detail.
Text Functions – LEFT
This function can be used to extract three characters from the left of a given string, this function can be used.
Swayam is using Excel to create a spreadsheet of all the subjects taught in his school and assign subject codes for each one of these for easy reference.
A subject code is made my using the first three alphabets of the given subject. He can do this easily using the LEFT function.
Syntax:
LEFT(text, num_chars)
Here, ‘text’ signifies the cell reference which contains the particular text and ‘num_chars’ indicates the number of characters to be extracted. In case ‘num_chars’ is not specified, by default, Excel extracts out only one character.
The formula he should use is
=LEFT(A2,3)
He can extend the formula to other cells of the column by simple dragging. The result will appear as follows
Do note that, number formatting does not affect the function. That is, the result will not change for ‘$4000’ and ‘4000’. The result will remain as ‘400’.
Text Functions – RIGHT
This function can be used to extract three characters from the right of a given string, this function can be used.
Suppose Swayam wants to create the subject codes not using the first three alphabets of the subject name but the last four, he can use the RIGHT function.
Syntax:
RIGHT(text, num_chars)
Here, ‘text’ signifies the cell reference which contains the particular text and ‘num_chars’ indicates the number of characters to be extracted. In case ‘num_chars’ is not specified, by default, Excel extracts out only one character.
The formula he should use is
=RIGHT(A2,4)
He can extend the formula to other cells of the column by simple dragging. The result will appear as follows
Do note that, number formatting does not affect the function. That is, the result will not change for ‘7500$’ and ‘7500’. The result will remain as ‘500’.
Text Functions – CONCATENATE
This function can be used to combine the text available in two different cells.
Swayam has inserted a column with the names of teachers taking these subjects. The table now looks as follows.
Suppose he wants to now create a row that combines the names of the subject and the teacher with the subject codes, for easy reference, he can use this function.
Syntax:
CONCATENATE(text1,text2,…)
Here, ‘text1’ and ‘text2’ signifies the cell references which contains the particular text.
The formula he should use is
=CONCATENATE(A2,B2)
He can extend the formula to other cells of the column by simple dragging. The result will appear as follows
The order of the cell references entered should be the order in which they should be combined.