# LEFT or RIGHT Formula?

#### audrey

##### Active Member
Hello,

I have these similar written in my excel worksheet. Is there a way to have the Name and Surname only ? It looks like I need a formula would find the " - " and take the rest.

Input
311-31 - AUDREY SURNAME
311-3135 - AUDREY2 SURNAME

Expected Output
AUDREY SURNAME
AUDREY2 SURNAME

Gokhan

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
If we can assume the " - " (space hyphen space) is a constant...It always precedes the name you want.

Try
=REPLACE(A1,1,FIND(" - ",A1)+2,"")

Hello,

I have these similar written in my excel worksheet. Is there a way to have the Name and Surname only ? It looks like I need a formula would find the " - " and take the rest.

Input
311-31 - AUDREY SURNAME
311-3135 - AUDREY2 SURNAME

Expected Output
AUDREY SURNAME
AUDREY2 SURNAME
Assuming your surnames are never one of the "modern-style" combined/hyphenated last names, you can use this formula...

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99))

Edit Note: If the spaces around the last hyphen will always be there, then use Jonmo1's formula (it would avoid the problem with hyphenated last names that I mentioned).

Last edited:
Audrey,

Excel 2007
AB
1311-31 - AUDREY SURNAMEAUDREY SURNAME
2311-3135 - AUDREY2 SURNAMEAUDREY2 SURNAME
3
Sheet1
Cell Formulas
RangeFormula
B1=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),100))
B2=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),100))

All work perfectly, thank you guys!

Audrey,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

Excel 2007
AB
1311-31 - AUDREY SURNAMEAUDREY SURNAME
2311-3135 - AUDREY2 SURNAMEAUDREY2 SURNAME
3

</TBODY>
Sheet1

Worksheet Formulas
CellFormula
B1=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),100))
B2=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),100))

</TBODY>

<TBODY>
</TBODY>
Just to point out, your formula will not produce the correct results for names with double-word first or last names. For example, I used to work with someone whose given first name was "Mary Ann" (with the space between them... Ann was not her middle name) and another whose last name was "Della Rossa" (again, with the space between them... Della was not his middle name).

Replies
0
Views
413
Replies
11
Views
225
Replies
4
Views
71
Replies
1
Views
139
Replies
3
Views
953

1,196,206
Messages
6,014,011
Members
441,802
Latest member
Aneurysm

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