CODEWITHSUNDEEP
excelvba
jardane
jardane

Reputation: 471

delete text left of space using excel vba

I am writing a macro that will scrub down an excel list. I need to take a list of names and remove the first name, so "John Smith" becomes "Smith". I know how to delete text after a space but before a space seems more tricky.

Upvotes: 0

Views: 5358

Answers (2)

Chronicide
Chronicide

Reputation: 1152

Here's a sub that loops through all cells in the named range called "rng_Names" and removes all but the last word.

Sub removeAllButLastWord()
    For Each cl In Range("rng_Names")
        cl.Value = Right(cl.Value, Len(cl.Value) - InStrRev(cl.Value, " "))
    Next cl
End Sub

Update

The following sub Just removes the first word:

Sub removeFirstWord()
    For Each cl In Range("rng_Names")
        cl.Value = Right(cl.Value, Len(cl.Value) - InStr(cl.Value, " "))
    Next cl
End Sub

Update the Second

The following sub removes just the first word, as well as the string " jr" if it exists

Sub removeFirstWordAndJR()
    For Each cl In Range("rng_Names")
        cl.Value = Replace(Right(cl.Value, Len(cl.Value) - InStr(cl.Value, " ")), " Jr", "", 1, -1, vbTextCompare)
    Next cl
End Sub

Upvotes: 0

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20775

use RIGHT("John Smith",LEN("John Smith")-FIND(" ","John Smith"))

Upvotes: 1

Related Questions