Inserting parantheses for one specific word in a excel column

[SlimD21]

Hi all,

I was trying to figure out the vba code for inserting a parantheses around the last name of a column in excel. I have a spreadsheet with over a 3000 entries. Ex. Lincoln Financial Advisors Corp. (Ahrens). That is all in one cell. But I just want the last name in parentheses. I currently have a code but wanted to drill it down to where it isolates that one specific word.
This is what I have:

Dim rng As Range, cl As Range, lastrng As Range
Dim Vu As String
On Error Resume Next
Set rng = Application.InputBox(prompt:= _
"Please Select First Cell in Column for Alteration ", _
Title:="Insert Brackets", Type:=8)

Set lastrng = Range(rng.Address, Cells(Rows.Count, 1).End(xlUp))

For Each cl In lastrng
Vu = cl.Value
cl.Value = "(" & Vu & ")"
Next

But it puts parentheses around the whole string in the column.
Any help would be greatly appreciated!

 

[SkipVought]

Hi,

first

 Set lastrng = Range(rng, Cells(Rows.Count, 1).End(xlUp))

the address property returns a STRING not a range.

dim i as integer

For Each cl In lastrng
  with cl
    i = instrrev(.Value," ")
    .Value = mid(.value,1, i) & "(" & mid(.value, i+1) & ")"
  end with
Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}