Excel Tips: String Skills – part 2, LEFT, RIGHT and MID functions


Excel Tips: LEFT, RIGHT and MID functions

Three of the most useful functions in excel are the LEFT, RIGHT and MID functions.

The LEFT function returns the number of specified characters, starting from the left of the string. If you had a string as below:

Thisis a test string

And you used the function: LEFT(A1, 10), the result would be:

Thisis a t

This is because a space is counted as a character. If you were to use the function: RIGHT(A1,10), the result would be:

est string

To examine the function, they work as such:

LEFT(location of string, how many characters you wish to return). This is the same as RIGHT, but it starts the count from the end of the string, not the start.

What happens if you want to return the middle of the string? Then you use the MID function. This is the code:

MID(location of the string, which character you want to start returning from, how many characters you wish to return).

So for example, using the code: MID(A1,5,10) will return:

is a test.

These can be used in combination, for example using the RIGHT and LEFT functions together. The code: RIGHT(LEFT(A1,6),2) works by first looking at the inner brackets, LEFT(A1,6). This returns:


Then taking RIGHT(Thisis,2), it returns:


Try out different combinations to see what results you can come up with! They are very useful functions that will help change daunting tasks into thirty seconds jobs!

Come check out the directory for the rest of my excel tips!