Use the Left or Right function in Excel to extract text from a cell

The Left and Right functions are very useful for managing text in Excel files.

Let’s imagine you have text in a field and you want to retrieve from it only the characters on the left. To give you an idea, the field has grades which are composed of 2 digits indicating the level of the employee (for example, 10 or 11) and a number of letters indicating the job family (for example, HR or F).

You want to extract only the job level information.

By using the Left function, you will easily get that data.

Go to Formula > Left and simply enter the cell of the text you want to use in the “text” field, and then the number of characters you want to extract in the “num_chars” field. In the example below : B3 and 2 will produce 12 from the original 12HR.

(email readers may have to click through to see the screen shot)

 Left function in Excel - Compensation Insider - Sunday Skill

Similarly, the Right function will enable you to get the “x” characters you need from a cell, counting from the end. It could be useful if for example your grades are composed of either 1 or 2 digits for the job level and one letter for the job family, such as 12C or 8H so you can’t use Text-to-Column as the column does not have the same number of characters in each cell.

These text functions in Excel will come in useful when you are trying to de-aggregate information that is entered into a single field in your HR system and will help you :

  • prepare salary review files
  • analyse job titles
  • compare grades across job families
  • extract user name from corporate email addresses etc

I hope you find this simple tip useful !

Related posts :

Print Friendly, PDF & Email

Speak Your Mind

*