left find right

[ppleasebob]

=LEFT(B11,FIND(" ",B11,1)-1)

returns the text from cell b11 that appears upto the space, in this case it returns the first name of an employee, how can I get it to return the surname (ie after the 1st space(*)), surely substituting LEFT for RIGHT should work.
This one is getting to me a bit, I was given the aforementioned formula by the board - thank you board! but I missed out on the explanation as to what bit does what and why..
please help - I'd like to learn.

oh the (*), I'd like to return Van Helsing from Robert Van Helsing if possible.

 

[facethegod]

Try this

=MID(B11,FIND(" ",B11,1)+1,len(B11))

 

[Gerald Higgins]

This worked for me

=RIGHT(B11,LEN(B11)-FIND(" ",B11,1))

 

[Gerald Higgins]

As to how they work, Excel help might help.
In your first example of

=LEFT(B11,FIND(" ",B11,1)-1)

there are basically two functions working together.

The first is LEFT, which returns the leftmost characters from a text string.
You need to tell it what your string is (B11) and how many characters you want to return.
So, if you wanted the first two characters of ROBERT VAN HELSING you would use

=left(B11,2)

which would return RO.
The problem here is that you want a different number of characters each time.
You can use a space to determine how many characters to return, but to do that, you need to find where the space occurs, and that's what the FIND part does.
It says find the first instance of space (" ") in your string, and return the character number for that instance. The 1 part tells it to start looking at the first character in your text string, but you could choose a different number.
Here are some examples, all from ROBERT VAN HELSING

=FIND(" ",B11,1) returns 7
=FIND(" ",B11,3) returns 5
=FIND(" ",B11,8) returns 3, referring to the second space.

and this leads on to another important point - are you sure you always want to break up your names on the first instance of " "?
What if you have a name such as ROBERT JOHN VAN HELSING ?
Anyway, I'll leave you to think about that.

Finally, the -1 is just a neat way of deducting 1 from the character number returned by FIND, so that you don't end up bringing a space into the text value that you end up with.
So you get ROBERT, instead of ROBERT_

So in summary, the FIND finds the first instance of " ", deducts one from the character number, and uses the result to drive your LEFT function.

 

[ppleasebob]

Face, Gerald,

Thanks for the two ways of working it

Gerald thank you for the explanation, it's easier to use when you know how it works,

Cheers bob