Find and remove right parenthesis and preceding 11 characters

kevinbradley57 · #1 02-20-2018, 09:37 PM

I need a macro that will search Column A for all instances of a right parenthesis and remove that right parenthesis and the preceding 11 characters.

Any help would be appreciated.

Thank you.

NoSparks · #2 02-21-2018, 08:43 AM

Might your cells contain more than one right parenthesis ?
If so, what is to happen if they are within 11 characters of each other ?

kevinbradley57 · #3 02-21-2018, 09:34 AM

Column A will always only have one right parenthesis and one left, and the content between will always be a six-character system ID. For example, my name is Kevin T Bradley and my system ID is ktbradl, so several cells in Column A will be populated with the following:
Bradley, Kevin T (KTBRADL)

I only need the first and last name to appear. I can live with the middle initial, so if it's easier to just remove the parentheses and the content between them that would be fine.

NoSparks · #4 02-21-2018, 11:00 AM

six-character system ID ?

Try this and the bracketed length and placement within the cell shouldn't matter

Code:

Sub RemoveParenthesized()

    Dim cel As Range, arr As Variant
    
For Each cel In Range("A1", Range("A" & Rows.Count).End(xlUp))
    If InStr(1, cel.Value, "(") > 0 And InStr(1, cel.Value, ")") > 0 Then
        arr = Split(Replace(Replace(cel.Value, "(", "|"), ")", "|"), "|")
        cel.Value = Trim(arr(0) & arr(2))
    End If
Next cel
End Sub

kevinbradley57 · #5 02-21-2018, 11:37 AM

Works great -- thanks!

kevinbradley57 · #6 02-21-2018, 05:10 PM

NoSparks,

The report just grew by several columns. What must I do to the code you provided in order to perform the operation on columns A, B, and J?

NoSparks · #7 02-21-2018, 05:58 PM

Need to change the range of cells to look at,
give this a try

Code:

Sub RemoveParenth_2()

    Dim rng As Range, cel As Range
    Dim lr As Long, arr As Variant
    
lr = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Set rng = Union(Range("A1:A" & lr), Range("B1:B" & lr), Range("J1:J" & lr))

For Each cel In rng
    If InStr(1, cel.Value, "(") > 0 And InStr(1, cel.Value, ")") > 0 Then
        arr = Split(Replace(Replace(cel.Value, "(", "|"), ")", "|"), "|")
        cel.Value = Trim(arr(0) & arr(2))
    End If
Next cel
End Sub

Debaser · #8 03-06-2018, 05:51 AM

Could you not just use the Find/Replace dialog (Ctrl+H) and replace " (*)" without the quotes?

kevinbradley57 · #9 03-13-2018, 12:21 PM

Perfect. Thank you!

kevinbradley57 · #10 03-13-2018, 02:20 PM

NoSparks - Perfect. Thank you.

kevinbradley57 · #11 03-25-2018, 05:38 PM

NoSparks -- Your code does exactly what I needed, but the "arr(2)" portion it appears to limit the number of names in the cell to two. There could be as many as 10 names in the cell, or as few as none. Can you help with that?

Sub RemoveParenth_2()

Dim rng As Range, cel As Range
Dim lr As Long, arr As Variant

lr = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Set rng = Union(Range("A1:A" & lr), Range("B1:B" & lr), Range("J1:J" & lr))

For Each cel In rng
If InStr(1, cel.Value, "(") > 0 And InStr(1, cel.Value, ")") > 0 Then
arr = Split(Replace(Replace(cel.Value, "(", "|"), ")", "|"), "|")
cel.Value = Trim(arr(0) & arr(2))
End If
Next cel
End Sub

NoSparks · #12 03-25-2018, 08:24 PM

Adapting what Debaser suggested...

Code:

Sub RemoveParenth_3()
    Dim rng As Range, lr As Long
lr = Cells.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Set rng = Union(Range("A1:A" & lr), Range("B1:B" & lr), Range("J1:J" & lr))
rng.Replace What:=" (*)", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _
                        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to remove first 12 characters of each cell	King Mustard	Excel	3	04-07-2017 12:06 AM
Can I remove spaces between only Chinese characters?	sivartnosredna7	Word	2	01-09-2017 05:33 AM
How do I Remove A Specific Amount of Characters from Lines in a Document?	tatihulot	Word	5	01-22-2016 05:55 AM
Find and Replace Parenthesis with Itallics	Richard Carr	Word	1	01-06-2015 06:44 PM
How to remove these unknown characters appearing	rajpes	Word	1	03-02-2012 03:24 AM