Extract 2nd to last word

[mozjesse]

How do I extract the second to last word from a string of text in one cell and put it another.

 

[MrKowz]

Do the strings of text have a consistency to them as far as how many spaces are in it?

 

[stephen antoine]

What is your data like?

Does each cell contain a different amount of words?

Is each word separated by spaces?

 

[PA HS Teacher]

It's no pretty, and someone may be around with a shorter answers, but...

If your text is in B1

Enter the following in C1

*** Make sure that after pasting the formula below into the formula bar, you press, Control, Shift, and Enter at the same time.

=MID(B1,LARGE((MID(B1,ROW(A$1:INDEX(A:A,LEN(B1))),1)=" ")*ROW(A$1:INDEX(A:A,LEN(B1))),2)+1,LEN(B1)-1-MAX((MID(B1,ROW(A$1:INDEX(A:A,LEN(B1))),1)=" ")*ROW(A$1:INDEX(A:A,LEN(B1)))))

 

[barry houdini]

With data in A1 try

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

 

[mozjesse]

That didn't work

The data looks like this: 01019000 - Los demás 10 A

What I want is to get the "10" but the number may be one to three digits and the description may be short or quite long... either way the string always ends in a number and then a letter... I want the number.

Thanks

 

[mozjesse]

That works, Thanks Barry!

 

[MrKowz]

Edit: Misposted

 

[Kenneth Hobson]

A UDF method:

'=RField(A1, 2)
Function RField(cell, pos As Integer, Optional delimit As String = " ")
  Dim a() As String
  a() = Split(cell, delimit)
  RField = a(UBound(a) - pos + 1)
End Function

 

[stanleydgromjr]

mozjesse,

I like Barry's solution (in the below screenshot, the entries in bold).


But, another way:

Excel Workbook
	A	B	C
1	This is a test to find the second to last word in a very long string.	long	long
2	This is a test to find the second to last word in a long string.	long	long
3	This is a test to find the second to last word in a string.	a	a
4	This is a test to find the second to last	to	to
5	01019000 - Los dems 10 A	10	10
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Option Explicit
Option Compare Text
Function Get_Word(text_string As String, nth_word) As String
'
' http://www.ozgrid.com/News/jul-2005.htm
'
Dim lWordCount As Long
    With Application.WorksheetFunction
        lWordCount = Len(text_string) - Len(.Substitute(text_string, " ", "")) + 1
        If IsNumeric(nth_word) Then
           nth_word = nth_word - 1
            Get_Word = Mid(Mid(Mid(.Substitute(text_string, " ", "^", nth_word), 1, 256), _
                .Find("^", .Substitute(text_string, " ", "^", nth_word)), 256), 2, _
                .Find(" ", Mid(Mid(.Substitute(text_string, " ", "^", nth_word), 1, 256), _
                .Find("^", .Substitute(text_string, " ", "^", nth_word)), 256)) - 2)
        ElseIf nth_word = "First" Then
            Get_Word = Left(text_string, .Find(" ", text_string) - 1)
        ElseIf nth_word = "Last" Then
            Get_Word = Mid(.Substitute(text_string, " ", "^", Len(text_string) - _
            Len(.Substitute(text_string, " ", ""))), .Find("^", .Substitute(text_string, " ", "^", _
            Len(text_string) - Len(.Substitute(text_string, " ", "")))) + 1, 256)
        End If
    End With
End Function

To use the "Get_Word" Function, I counted the number of spaces in the string with:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

The formula/function in cell B1 (copied down) (to get the second to last word):
=Get_Word(A1,LEN(A1)-LEN(SUBSTITUTE(A1," ","")))


Have a great day,
Stan