LEN and TRIM Functions in MS Excel

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.

LEN and TRIM 1

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

LEN and TRIM 2

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

LEN and TRIM 4She 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

LEN and TRIM 3

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.

LEN and TRIM 5

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.

Leave a Comment

Your email address will not be published. Required fields are marked *