Excel / Google Sheets formulas: LEFT, RIGHT, SEARCH, LEN, CONCATENATE

I am continuing a small series of articles about formulas that we can use in both Excel and Google Sheets. I find formulas very useful in helping me automate complex tasks and be overall more efficient.

In this post I am want to put into context 4 formulas that deal with text: left, right, search, concatenate. I’ve put these formulas together because they can be very useful in working with names.

Our example

We have this list:

We want to:

• extract the last name from the first column
• extract the first name from the first column
• put the name back together in the reverse order in the last column

Obviously we don’t want to use COPY and PASTE, but rather use formulas to make life easier for us.

Last name

Let’s start easy by extracting the last name. For this we will use the LEFT and SEARCH functions together. LEFT for extracting some characters and search for determining the number of chars to extract. This is the formula:

=LEFT(B2,SEARCH(” “,B2))

The SEARCH formula will find the blank character and return its position from left to right thus giving the LEFT function the exact number of characters to extract. The result so far:

First name

Moving on to the First Name. It would be easy to think that by just replacing LEFT in the formula above with RIGHT we will have our result. We won’t because the search function gives us the length of the first word and not the second. To find out the length of the second word I will need to know the length of the full name and then subtract the first word, something like this:

LEN(B2)-SEARCH(” “,B2)

That makes the formula to extract the first name look like this:

=RIGHT(B2,LEN(B2)-SEARCH(” “,B2))

And the result so far:

Full name reversed

Lastly, we want to put the first name and the last name back together, this time however in the reverse order. For this we will use the CONCATENATE formula. This formula sticks together 2, 3, 1000, … cells and text.

This is how I adapted the formula to our case. Notice that I added a space between the First and The Last name.

=CONCATENATE(D2,” “,C2)

And our quest is completed:

Download

You can download the document I used here.

More about the formulas I used

• LEFT: extracts a given number of characters from a given string starting from the left
• RIGHT: basically the same thing, but starting from the right
• LEN: returns the length of a string
• SEARCH: a function that searches a character in a string and when it finds it returns the position number starting from the left
• CONCATENATE: a function that sticks together 2,3, … strings