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]

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 correction may be extremely simple, but without know what you text looks like, it is hard to say. Is there always a comma/space between words?

 

[adambelnap]

Two questions:
Is it ever possible that the second to last word is the first word?

Can I assume the comma is at the end of the string? If so you cpuld nest the formula you have in this:

LEFT((your formula),LEN(your formula)-1)

 

[brucef2112]

As you have it your data is in A1. Your existing formula shown above is in B1.
Place this formula in C1
If there is no punctuation it returns the word (as your formula aready does).
If there is punctuation it removes it and returns the word.

=IF(ISERROR(MATCH(RIGHT(TRIM(B1),1),{",",".",":",";","?"},0)), B1, LEFT(B1,LEN(B1)-1))

Notice the values in the curly brackets. It will find any of these characters and strip them off if found. If there are others, just put them inside the curly brackets between "". Each one has to have a comma between them as shown. If you only need the comma search you can remove the others and just leave the comma one inside the curly brackets.

 

[Vidar]

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.

Based on your formula:
=SUBSTITUTE(TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A2)," ",REPT(" ",60)),120),60)),",","")

 

[chrono2483]

Thanks everyone for your help - I was able to use adambelnaps suggestion, and have the following formula:

=IFERROR(LEFT((TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1)," ",REPT(" ",60)),120),60))),LEN(TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1)," ",REPT(" ",60)),120),60)))-1),"")

 

[Rick Rothstein]

Thanks everyone for your help - I was able to use adambelnaps suggestion...

Any chance of getting you to answer the question I asked in Message #2?

 

[brucef2112]

Here is a function I wrote to do the same but will be easier, as it is short, more manageable, and easy to read when used as a formula in a cell.
It also has a tremendous flexibility to be used elsewhere.

It will return the nth word you specify and if there are non-alpha characters attached they will be removed.
FindWord("string value", Word Position# sought)

In a cell, =FindWord(A1, -2) will return the 2nd to last word in the cell A1. (note the negative number passed)

You can retrieve the nth word starting from the Left or Right side.
If 'Position number' is POSITIVE it fetches nth word from LEFT to RIGHT.
If 'Position number' is NEGATIVE it fetches nth word from RIGHT to LEFT.

It allows a Hyphen in a word string. ie "Van-Stewart" will return "Van-Stewart".
If word begins or ends in a Hyphen, the Hyphen will be removed.ie "Over-" will return "Over".
If a hyphen is separated by spaces from other words, it is seen as a 'word'. ie " - " will return "-".
Otherwise only numbers and alpha characters are returned. ie "this," will return "this".
Languages that uses special characters or is specified in the system locale settings may break this(I can't test for this).
These accented characters À,Ê,à,ê,Ø,ø will work fine.

Example formula:
=FindWord("Her last name is Van-Stewart, she has goats, cats- and -fish.", 1) returns "Her"
=FindWord("Her last name is Van-Stewart, she has goats, cats- and -fish.", 3) returns "name"
=FindWord("Her last name is Van-Stewart, she has goats, cats- and -fish.", -1) returns "fish" Note: Period removed
=FindWord("Her last name is Van-Stewart, she has goats, cats- and -fish.", 8) returns "goats" Note: trailing comma removed
=FindWord("Her last name is Van-Stewart, she has goats, cats- and -fish.", -3) returns "cats" Note: trailing hyphen removed
=FindWord("Her last name is Van-Stewart, she has goats, cats- and -fish.", 5) returns "Van-Stewart" Note: hyphen isn't removed

Dealing with spaces, or no spaces, around a hyphen:
=FindWord("I have a house - not a- mouse.", 5) returns "-" Note: hyphen surrounded by spaces is seen as a 'word'.
=FindWord("I have a house -not a- mouse.", 5) returns "not"

Both the STRING and the POSITION can be Cell references. =FindWord(A1, B1)

To place this function in a Module
From your spreadsheet hit [Alt]+[F11] to open the Visual Basic Editor.
On the menu bar select Insert > Module
Paste this code in the code window.

use in cells: =FindWord(string to search, Word Position# sought)

Function FindWord(Source As String, Position As Integer) As String
Dim arr() As String
Dim sTmp As String, sChar As String, sClean As String
Dim xpos As Integer
    ' If Position number is POSIITIVE it fetches nth word from LEFT to RIGHT.
    ' If Position number is NEGATIVE it fetches nth word from RIGHT to LEFT.
    '
    ' Note: We are allowing a Hyphen in a word string. ie "[COLOR=#333333]Van-Stewart[/COLOR]" will return "[COLOR=#333333]Van-Stewart[/COLOR]".
    ' If word begins or ends in a Hyphen, the Hyphen will be removed.ie "Over-" will return "Over".
    ' Otherwise only numbers and alpha characters are returned. ie "this," will return "this".
    ' Languages that uses special characters or is specified in the system locale settings may break this.
    ' These accented characters  À,Ê,à,ê,Ø,ø are OK.
    xpos = 0
    sTmp = Trim(Source)
    
    Do Until InStr(sTmp, "  ") = 0      ' Loop until there are no more double spaces
        sTmp = Replace(sTmp, "  ", " ") ' Replace 2 spaces with 1 space
    Loop
    arr = Split(sTmp, " ")
    
    xCount = UBound(arr)
    
    If Position > 0 Then xpos = Position
    If Position < 0 Then xpos = xCount + Position + 2
    
    sTmp = Empty
    If Not (((xpos - 1) > xCount) Or (xpos = 0)) Then sTmp = arr(xpos - 1)
    ' Check for characters to remove.
    For i = 1 To Len(sTmp)
        sChar = Mid$(sTmp, i, 1)
        If sChar Like "[-0-9A-Za-zÀ-ø]" Then sClean = sClean & sChar
    Next i
    If sClean <> "-" Then ' If the string is just a hyphen(-) return it as the word.
        If Left(sClean, 1) = "-" Then sClean = Mid(sClean, 2, Len(sClean))
        If Right(sClean, 1) = "-" Then sClean = Mid(sClean, 1, Len(sClean) - 1)
    End If
    
    FindWord = sClean
    
End Function

As with any spreadsheet with code modules, be sure to allow macros to be run when spreadsheet is opened.

 

[chrono2483]

Hi Rick,

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

Thanks!

 

[brucef2112]

-Bruce mumbles to himself *I hate the 10 min edit limit.*

Wouldn't it be nice to know how many words are in the string.
I modified the Function to do this.
It all works the same as before.
Pass a POSITIVE or NEGATIVE position number returns the nth word.
However, Now if you pass it a ZERO, it will return the number of words in the string.

=FindWord("Bob has a dog.", 2) returns "has"
=FindWord("Bob has a dog.", -2) returns "a"
=FindWord("Bob has a dog.", 0) returns "4" (the word count in the sentence)

Could be useful for other string manipulations.

Function FindWord(Source As String, Position As Integer)Dim arr() As String
Dim sTmp As String, sChar As String, sClean As String
Dim xpos As Integer
    ' If Position number is POSITIVE it fetches nth word from LEFT to RIGHT.
    ' If Position number is NEGATIVE it fetches nth word from RIGHT to LEFT.
    ' If Position number is ZERO it returns the number of words found in string.
    '
    ' Note: We are allowing a Hyphen in a word string. ie "Van-Stewart" will return "Van-Stewart".
    ' If word begins or ends in a Hyphen, the Hyphen will be removed.ie "Over-" will return "Over".
    ' Otherwise only numbers and alpha characters are returned. ie "this," will return "this".
    ' Languages that uses special characters or is specified in the system locale settings may break this.
    ' These accented characters  À,Ê,à,ê,Ø,ø are OK.
    xpos = 0
    sTmp = Trim(Source)
    
    Do Until InStr(sTmp, "  ") = 0      ' Loop until there are no more double spaces
        sTmp = Replace(sTmp, "  ", " ") ' Replace 2 spaces with 1 space
    Loop
    arr = Split(sTmp, " ")
    
    xCount = UBound(arr)
    
    If Position > 0 Then xpos = Position
    If Position < 0 Then xpos = xCount + Position + 2
    If Position = 0 Then
        FindWord = xCount + 1
        Exit Function
    End If
    
    sTmp = Empty
    If Not (((xpos - 1) > xCount) Or (xpos = 0)) Then sTmp = arr(xpos - 1)
    ' Check for characters to remove.
    For i = 1 To Len(sTmp)
        sChar = Mid$(sTmp, i, 1)
        If sChar Like "[-0-9A-Za-zÀ-ø]" Then sClean = sClean & sChar
    Next i
    If sClean <> "-" Then ' If the string is just a hyphen(-) return it as the word.
        If Left(sClean, 1) = "-" Then sClean = Mid(sClean, 2, Len(sClean))
        If Right(sClean, 1) = "-" Then sClean = Mid(sClean, 1, Len(sClean) - 1)
    End If
    
    FindWord = sClean
    
End Function