excel macro to get last word from SELECTED RANGE

A

aswathy0001

New member
Joined
Nov 29, 2016
Messages
24
Reaction score
0
Points
0
Location
india
Excel Version(s)
2010
i get the last word from a single cell or from the complete range but
How i get the last word for all the selected cells?
eg if is select range("A5:A10") then i get the last words from that cell. how it possible
 
Hi Aswathy,

the below code should do it for you.

Code: 
Option Explicit


Sub GetLastWordInSelectedRange()
    Dim rng As Range, c As Range
    Dim sLastWord As String
    
    Set rng = Application.Selection


    'Loop through all cells in the range, grabbing the last word each time
    For Each c In rng
        sLastWord = Right(c, Len(c) - (InStrRev(c, " ")))
    Next c
    
    'After the loop is completed, sLastWord will equal the last word in the last cell
    MsgBox sLastWord
    
End Sub

thanks,
Seán
 
Thank you but i need to print the last word in offset 1 each cell
 
That's even easier:

Code: 
Option Explicit


Sub GetLastWordInSelectedRange()
    Dim rng As Range, c As Range
    Set rng = Application.Selection


    'Loop through all cells in the range, grabbing the last word each time
    For Each c In rng
        c.Offset(0, 1) = Right(c, Len(c) - (InStrRev(c, " ")))
    Next c
    
End Sub
 
You must log in or register to reply here.
Back
Top