Functions are the most useful elements of Excel. These are predefined formulas that one may use for one’s easy calculations. Two important text formulas amongst these are ‘LEN’ and ‘TRIM’.
‘LEN’ and ‘TRIM’ – LEN Function
This function can be used to detect the length of a string of characters. This maybe numbers, dates or plain text.
For example,
Shradha has the following data in her sheet.
The management has decided that everyone with a five digit salary will get a 10% raise and everyone with a six digit salary will get a 12% raise. To calculate this, she can use the LEN function.
Syntax:
=LEN(text)
Here ‘text’ signifies the cell that contains the text to be counted characters of.
The formula she should, thus, use is
=LEN(B2) and drag it to the rest of the rows. The result will appear as follows
She can now easily calculate the new salary of all the employees.
This can also be inserted by clicking on the ‘LEN’ option from the ‘Text’ dropdown list in the ‘Function Library’ of ‘Formulas’ menu. The following dialogue box will appear
She can now enter the cell containing the text in the ‘Text’ textbox and click ‘Ok’ once done.
An important point to note in this function is that the number formatting is not included while counting. That is, in the above cells, though the amount is displayed with two decimal places, these are not taken into account.
‘LEN’ and ‘TRIM’ – TRIM Function
This function is helpful in removing unnecessary spaces in the cells. This will clean
- extra spaces and replace them with a single one
- unwanted spaces before or after a data entry
- extra line breaks
Syntax:
=TRIM(text)
Here ‘text’ signifies the cell that contains the text to be trimmed.
in the above example, we can observe that some of the employee codes have spaces between them, by mistake. This can be rectified using the TRIM function. The formula she should use is
=TRIM(A2) and then drag for all the cells of the column so that no cell is missed out. The result will appear as follows
This can also be inserted by clicking on the ‘TRIM’ option from the ‘Text’ dropdown list in the ‘Function Library’ of ‘Formulas’ menu. The following dialogue box will appear.
She can now enter the cell containing the text in the ‘Text’ textbox and click ‘Ok’ once done.
TRIM function is especially useful while dealing with files that have come from somewhere else.