DOC Office Consulting

Left & Find

Hi there!

In this brand new tiny topic you're going to learn how to create a formula that will turn a name from First Name Last Name format into Last Name, First Name format, like this:

There's just a few simple functions that you're going to need to know – in fact you may have already mastered them. These are LEFT, LEN, FIND & RIGHT.

We'll start this tutorial off with LEFT and FIND.

• LEFT will return a specified number of characters from the left of a string of text (i.e. if the string of text is my name : Danielle O'Connell, returning 8 characters from the left of the string will give you just my first name: Danielle)
• FIND is the function that we'll use to find the number of characters in my first name, because as you'll know – not everyone has the same number of characters in their name, so we'll need Excel to find out how many there are.
  FIND looks for a specific string of text (in this case it'll be a space) within another string of text and returns the position of that character. In the example below, we can see that the space (between Jane and Hallow) is in position number 5.

To get the number of characters in the first name, we'll get Excel to find the position of the space, then deduct 1. To work through the activity, download this activity file and play the video.

Alrighty,

Hopefully you've now you've mastered the LEFT and FIND functions to find the position of the space to return the characters to the left of it (i.e. FIRST NAME), so we can move on to returning the Surname using RIGHT and LEN

• RIGHT will return a specified number of characters from the right of a string of text
  (i.e. if the string of text is my name : Danielle O'Connell, returning 9 characters from the right of the string will give you just my surname: O'Connell)
• LEN is the function that we'll use to find the number of characters in the full name, which we'll then combine with FIND (which we've already covered), to deduct the number of characters to the left of the space – resulting with the number of characters to the right of the space – which is what we'll need to calculate the number of characters in anyone's surname – rather than just mine.

i.e. In this example LEN will return 11 (the total number of characters in the string).
Find will return 5 (the position of the space),
hence the number of characters in the surname will be 6: 11-5=6

Great work!

By now you should understand all of the functions that we're going to use to turn this: Matt Powers, 
POWERS, Matt.

In this lesson we're simply going to combine LEFT, RIGHT, FIND and LEN to return the last name and then the first name. You're going to be absolutely fine and you're going to be able to use this formula again and again with any name. You're also going to be able to reverse it with just a little bit of thinking: i.e. to turn POWERS, Matt into Matt Powers, using the exact same functions.

I know that I use variations of these functions all the time and I truly hope you can put what you've learnt in this tiny topic to some practical use too.

Cheers,

Dani xo