You can do this quite easily manually. Select the entire column and click Edit/Replace from the menu bar. Put " now " (without the quote marks... just make sure you have a blank space at the beginning and end of the text) into the "Find what" field, put any character that will never be found in a name (I am using the greater than symbol, ) into the "Replace with" field, click the "Options" button (note the double greater than symbol) to open the extra options and if there is a check mark in the "Match entire cell contents", remove it. Now click the "Replace All" button. Next, while the column is still selected, click Data/TextToColumns from the menu bar... click "Delimited" on Step 1, click "Other" and put the symbol () in the blank field next to it on Step 2 and then click the "Finish" button. And, if you want a macro to do all that, something like this should work...

Sub SplitOutMarriedNames()
Dim C As Range
For Each C In Range("A:A")
If InStr(1, C.Value, " now ", vbTextCompare) Then
C.Value = Replace(C.Value, " now ", "", , , vbTextCompare)
End If
Next
Range("A:A").TextToColumns Range("A1"), xlDelimited, , , _
False, False, False, False, True, ""
End Sub

--
Rick (MVP - Excel)


"Michael Koerner" wrote in message ...
I have Heading = Last Name in Col A This column has a lot of names that contain (now xxxxx) which is their married name I would like to move all of the (now xxxxx) to Col B which has a heading of Married Name so that I can sort properly on Col A. Can this be done easily or do I have to do it one record at a time.

--

Regards
Michael Koerner