Delete text from a cell.... with a twist

excel_2009

Active Member
Joined
Sep 14, 2009
Messages
318
hi excel gurus,

I've the following issue and was wondering if someone could help me. I have a column full of text and numbers, all i want to do is keep all the numbers which are in a bracket. sample data:

spiderman
batman101
peter(101)

and all i'll be left with on the sheet would be (101)

can someone please help?

I have this subsitute forumla but cannot for the life of me figure out how to adapt to my needs:

Code:
=SUBSTITUTE(A1," "&LEFT(A1,LEN(A1)-SEARCH("@#",SUBSTITUTE(A1," ","@#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),"")

Thank you :)
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
or something like this:

Code:
Function RemoveNumeric(Rng As String) As String
Dim Tmp As String
Dim i As Integer

Tmp = Rng
For i = 0 To 9
Tmp = Application.Substitute(Tmp, i, "")
Next i
RemoveNumeric = Tmp


End Function

but how do i adapt it so that it keeps the number aswell as the bracket and removes all the a-z characters?

thank you :)
 
Upvote 0
Try this VB on a copy of your data.

Code:
Sub excel_2009()
    Application.ScreenUpdating = False
    With CreateObject("vbscript.regexp")
        .Pattern = "[a-z]"
        .IgnoreCase = True
        .Global = True
        For Each c In ActiveSheet.UsedRange
            v = c.Value
            If InStr(v, "(") Then
                c.Value = "'" & .Replace(v, "")
            Else
                c.ClearContents
            End If
        Next
    End With
End Sub
 
Upvote 0
Try this VB on a copy of your data.

Code:
Sub excel_2009()
    Application.ScreenUpdating = False
    With CreateObject("vbscript.regexp")
        .Pattern = "[a-z]"
        .IgnoreCase = True
        .Global = True
        For Each c In ActiveSheet.UsedRange
            v = c.Value
            If InStr(v, "(") Then
                c.Value = "'" & .Replace(v, "")
            Else
                c.ClearContents
            End If
        Next
    End With
End Sub

Hi Weaver,

I seem to get one small issue with this method, can it be adapted so that all fullstops are also removed? and also, there are instances where there are numbers outside of the brackets that sometimes get included e.g:
118Peter.co.uk (111219) would show as 118.. (111219)

How can this be resolved? Thank you!






Thank you so much!!
 
Last edited:
Upvote 0
You can eliminate full stops by adding one to the pattern

Code:
.Pattern = "[a-z.]"
The other issue is more problematic - the solution was designed with the thought in mind that there was only unwanted text outside of the brackets. The pattern matching in regular expressions (regexp) is very powerful and what you're asking for might be possible, but I have to confess to being a bit of a beginner in its use. I'll have a think about it.
 
Upvote 0
Hi weaver, thank you for the reply. I did manage to find a workaround (admittedly its long winded) but here hoes:

Code:
Sub excel_2009()
    Application.ScreenUpdating = False
    With CreateObject("vbscript.regexp")
        .Pattern = "[a-z]"
'           .Pattern = "[.]"
'           .Pattern = "[0-9 (]"
        .IgnoreCase = True
        .Global = True
        For Each c In ActiveSheet.UsedRange
            v = c.Value
            If InStr(v, "(") Then
                c.Value = "'" & .Replace(v, "")
            Else
                c.ClearContents
            End If
        Next
        
        Application.Run "'C_QA.xlsm'!Macro4"
        
    End With
    
End Sub










Sub Macro4()

    Application.ScreenUpdating = False

    Cells.Replace What:="*(", Replacement:="(", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("B:B").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.NumberFormat = "@"
    
    Range("B:B").Hyperlinks.Delete
    
    Columns("B:B").Select
    Selection.Copy
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Columns("B:B").Delete

Cells.Select
    Range("E17").Activate
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
   
    
        Range("A:A").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.NumberFormat = "@"
   
     Cells.Replace What:="(", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=")", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
          Range("A1").Select
        
End Sub
 
Upvote 0

Forum statistics

Threads
1,226,284
Messages
6,190,053
Members
453,591
Latest member
plengeb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top