# left find right

##### Board Regular
=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..

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

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### facethegod

##### Well-known Member
Try this

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

#### Gerald Higgins

##### Well-known Member
This worked for me
Code:
``=RIGHT(B11,LEN(B11)-FIND(" ",B11,1))``

#### Gerald Higgins

##### Well-known Member
As to how they work, Excel help might help.
Code:
``=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
Code:
``=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
Code:
``````=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.

##### Board Regular
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

Replies
7
Views
513
Replies
6
Views
191
Replies
3
Views
214
Replies
4
Views
1K
Replies
8
Views
309

1,190,700
Messages
5,982,362
Members
439,776
Latest member
mathewduffy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back