CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-2010
Tetora
Tetora

Reputation: 433

How to get last word in a cell using Excel Formula

I am trying to get the last word in a cell however, it does not seem to be appearing getting the last word of the cell. It seems to get the partial match and not working correctly. I am using =IFERROR(RIGHT(AP2,SEARCH(" ",AP2)-1),AP2). This works great for getting the first word in a cell even where the first word is the only word in a cell. Any ideas on why this is not getting the last word correctly? enter image description here

Upvotes: 0

Views: 4544

Answers (3)

QHarr
QHarr

Reputation: 84465

Your formula is based on the first occurrence of a space in the string.

For example with "Man City V Stoke" in the formula SEARCH(" ",AP11) is looking for the first occurrence of a space which occurs at position 4. You are then subtracting 1 from this and using this as the number of characters to return from the right of the original string. So you get Right("Man City V Stoke",3) which is "oke".

A better way is a formula such as

=TRIM(RIGHT(SUBSTITUTE(AP11," ",REPT(" ",LEN(AP11))),LEN(AP11)))

find text after last space The part written: =SUBSTITUTE(AP1," ",REPT(" ",LEN(AP1)))

This inserts spaces the length of the string (16) in place of every occurrence of a space i.e.

Man                City                V                Stoke

When you then do RIGHT(SUBSTITUTE(AP11," ",REPT(" ",LEN(AP11))),LEN(AP11)) you are guaranteed to only get whitespace followed by the last word. You then use TRIM to get rid of this white space.

Upvotes: 1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

You may try this UDF which can be used in another sub routine or on the worksheet as well.

Function GetLastWord(ByVal Str As String) As String
Dim arrStr
arrStr = Split(Str, " ")
GetLastWord = arrStr(UBound(arrStr))
End Function

If you need to use it on the worksheet, assuming your string is in A1 then try this...

=GetLastWord(A1)

Upvotes: 1

Tetora
Tetora

Reputation: 433

As far as I can tell the below worked for me.

=TRIM(RIGHT(SUBSTITUTE(AP40," ",REPT(" ",100)),100))

I can only assume my other method is unreliable at getting the last word.

Upvotes: 0

Related Questions