Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-20-2018, 09:37 PM
kevinbradley57 kevinbradley57 is offline Windows 7 64bit Office 2010 64bit
Advanced Beginner
 
Join Date: Jul 2017
Posts: 46
kevinbradley57 is on a distinguished road
Default Find and remove right parenthesis and preceding 11 characters

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.
Reply With Quote
  #2  
Old 02-21-2018, 08:43 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 561
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Might your cells contain more than one right parenthesis ?
If so, what is to happen if they are within 11 characters of each other ?
Reply With Quote
  #3  
Old 02-21-2018, 09:34 AM
kevinbradley57 kevinbradley57 is offline Windows 7 64bit Office 2010 64bit
Advanced Beginner
 
Join Date: Jul 2017
Posts: 46
kevinbradley57 is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 02-21-2018, 11:00 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 561
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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
Reply With Quote
  #5  
Old 02-21-2018, 11:37 AM
kevinbradley57 kevinbradley57 is offline Windows 7 64bit Office 2010 64bit
Advanced Beginner
 
Join Date: Jul 2017
Posts: 46
kevinbradley57 is on a distinguished road
Default

Works great -- thanks!
Reply With Quote
  #6  
Old 02-21-2018, 05:10 PM
kevinbradley57 kevinbradley57 is offline Windows 7 64bit Office 2010 64bit
Advanced Beginner
 
Join Date: Jul 2017
Posts: 46
kevinbradley57 is on a distinguished road
Default

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?
Reply With Quote
  #7  
Old 02-21-2018, 05:58 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 561
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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
Reply With Quote
  #8  
Old 03-06-2018, 05:51 AM
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 151
Debaser is on a distinguished road
Default

Could you not just use the Find/Replace dialog (Ctrl+H) and replace " (*)" without the quotes?
Reply With Quote
  #9  
Old 03-13-2018, 12:21 PM
kevinbradley57 kevinbradley57 is offline Windows 7 64bit Office 2010 64bit
Advanced Beginner
 
Join Date: Jul 2017
Posts: 46
kevinbradley57 is on a distinguished road
Default

Perfect. Thank you!
Reply With Quote
  #10  
Old 03-13-2018, 02:20 PM
kevinbradley57 kevinbradley57 is offline Windows 7 64bit Office 2010 64bit
Advanced Beginner
 
Join Date: Jul 2017
Posts: 46
kevinbradley57 is on a distinguished road
Default

NoSparks - Perfect. Thank you.
Reply With Quote
  #11  
Old 03-25-2018, 05:38 PM
kevinbradley57 kevinbradley57 is offline Windows 7 64bit Office 2010 64bit
Advanced Beginner
 
Join Date: Jul 2017
Posts: 46
kevinbradley57 is on a distinguished road
Default

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
Reply With Quote
  #12  
Old 03-25-2018, 08:24 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 561
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 10:29 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft