Extract 2nd to last word

[chrono2483]

Hello,

I found an old thread from 2008 that helped with extracting the 2nd last word from a string of text. However, when I apply it to my situation, it adds the comma that is attached to the word. Is there any way of getting this work without the comma?

formula used: =TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1)," ",REPT(" ",60)),120),60))

The string of text can vary.

Thank you.

 

[Rick Rothstein]

Yes, there would always be a comma and a space after the work in question: Ex. Simpson, Homer .... Doe, John .... Smith, Paul

Hmm! Last name followed by first name... what is located between any two names (where I have the red question mark below), a space, a comma/space, something else?

LastName1, FirstName1?LastName2, FirstName2?LastName3, FirstName3

Also, from the above example, what do you want returned as the second to last "word"...

LastName3

or

LastName2, FirstName2

 

[chrono2483]

Hmm! Last name followed by first name... what is located between any two names (where I have the red question mark below), a space, a comma/space, something else?

LastName1, FirstName1?LastName2, FirstName2?LastName3, FirstName3

Also, from the above example, what do you want returned as the second to last "word"...

LastName3

or

LastName2, FirstName2

Here is a visual of how the data currently looks:

Simpson, Homer

Doe, John

Doe, Jane

Jackson, Paul

Belle, Rogue

<tbody>
</tbody>

The data is in a single column, with the rows dynamic, depending on how many names are pulled that particular day. What I want to be able to do is return only the last name in a separate column.

Ex:

Simpson, Homer	Simpson
Doe, John	Doe
Doe, Jane	Doe
Jackson, Paul	Jackson
Belle, Rogue	Belle

<tbody>
</tbody>

 

[Rick Rothstein]

Here is a visual of how the data currently looks:

Simpson, Homer

Doe, John

Doe, Jane

Jackson, Paul

Belle, Rogue

<tbody>
</tbody>

The data is in a single column, with the rows dynamic, depending on how many names are pulled that particular day. What I want to be able to do is return only the last name in a separate column.

Ex:

Simpson, Homer	Simpson
Doe, John	Doe
Doe, Jane	Doe
Jackson, Paul	Jackson
Belle, Rogue	Belle

<tbody>
</tbody>

Your original question asked for the second to last word which made it sound like you had several names in your cell (that is, more than 2 of them) and that you wanted the next to last name; but actually, your cells contain a single name and you want the first name from it...

=LEFT(A1,FIND(",",A1&",")-1)