LEFT or RIGHT Formula?

[audrey]

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

Thanks in advance.

Gokhan

 

[Jonmo1]

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,"")

 

[Rick Rothstein]

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).

 

[hiker95]

Audrey,

How about:

Excel 2007
	A	B
1	311-31 - AUDREY SURNAME	AUDREY SURNAME
2	311-3135 - AUDREY2 SURNAME	AUDREY2 SURNAME
3
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),100))
B2	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),100))

 

[audrey]

All work perfectly, thank you guys!

 

[hiker95]

Audrey,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

 

[Rick Rothstein]

Excel 2007

	A	B
1	311-31 - AUDREY SURNAME	AUDREY SURNAME
2	311-3135 - AUDREY2 SURNAME	AUDREY2 SURNAME
3

<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0"><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula B1 =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),100)) B2 =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),100)) <THEAD> </THEAD><TBODY> </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).