Custom Search and Replace Macro - Please help

[gregw74]

Thank you in advance to anyone who may might be willing and able to help me here.

I have a worksheet where every cell has information in the format of:

First Name) Richard
Last Name) Simmons
etc...

The end result I'm looking for is

Richard
Simmons
etc...

How would I write a macro that would simply replace all characters to the left of the first closing parenthesis of each cell, plus one character to include the space that follows. EX: "xxxxxx) "?

Thank you,
Greg

[Kaak]

[Greg M]

Hi there (from one Greg to another!)

There are two ways to do what you want.

The first way uses Excel. If the first of your data cells is A1 then insert the following formula in cell B1 & copy it all the way down beside your data:

=IF(LEFT(A1,  12) = "First Name) ",  MID(A1,  13,  999),  IF(LEFT(A1,  11) = "Last Name) ",  MID(A1,  12,  999),  "Error"))

This trims the unwanted first part of the text & you can then use Copy and Paste Special > Values to overwrite the original data.

The second way uses Excel VBA. Insert the following code into a VBA module in your workbook:

Option Explicit


Sub ProcessText()

    Dim cel As Range
    Dim txt As String

    With Selection
        For Each cel In .Cells
            txt = cel.Value
            If InStr(txt, ")") <> 0 Then
                cel.Value = Mid(txt, InStr(txt, ")") + 2, 999)
            End If
        Next cel
    End With

End Sub

Then just select the range you want to process & run the above routine.

Hope this helps - any feedback would be appreciated.

Gest regards,

Greg M

[gregw74]

Brilliant!! Many thanks to both of you, and for the insane turn-around time. I tested both macros and they worked like a champ! This is exactly what I was looking for!

Thank you!!