macro to delete certain data in a cell

[james1432]

any help with this one appreciated i am new to macro's and vba but came across a problem i need to delete certain data from all cells in column c to be specific numerical values in brackets and leave the text and the text in brackets alone.I tried text to columns as well but not working for me.

any help ?

[JBeaucaire]

Sure, post a sample file with an exact representation of the data, 10-20 cells worth in column C. Make sure we see the original data and a mockup of the expected results.

[james1432]

here is column c data
location
Sandton, N1(dap)(10,-321)
midrand,n3 (1233,-109)
boksburg 6 km,(wtt musina)(10,-329)
Sandton, N1(CROSSROADS)(10,-321)
and here is how i would like the data displayed

location
Sandton,N1 dap
midrand,n3
boksburg 6 km wtt musina
sandton,n1 crossroads

Thank you in advanced..
James

[Winon]

Hello james1432,

Welcome to the Forum.

While I am not sure what your data looks like, you may try the following Code. Just maybe it works for you.

Sub Extract_Numbers_In_Bracs()

Dim nCell As Range
Dim Sval

For Each nCell In Range("C1:C500")
    If InStr(nCell.Value, "(") And InStr(nCell.Value, "(") Then
        Sval = Mid(nCell.Value, InStr(nCell.Value, "(") + 1, Len(nCell.Value))
        nCell.Value = Left(Sval, InStr(Sval, ")") - 1)
    End If
Next nCell

End Sub

[james1432]

Hello Winon

Thank you for this code but it is not doing what i hoped it will as i needed to keep the text and delete the numerical values between the brackets your code keeps the numerical and deleted the text i needed tho , it kept the text that was in brackets here is a sample line with explanation what i need ,the code is close what i need so it seems you put me on the right track.

Johannesburg (dap) (wtt musina) (103.-91000) the data differs tho on each cell in column c i need to keep all text and text between brackets and the numericals deleted. so it should look like this .....Johannesburg dap wtt musina and the rest deleted..

[Winon]

Hello james1432,

To be honest, I do not know how to handle the variations,i.e.,

Sandton, N1(dap)(10,-321)
midrand,n3 (1233,-109)
boksburg 6 km,(wtt musina)(10,-329)
Sandton, N1(CROSSROADS)(10,-321)

But I am sure that JBeaucaire has the solution.

Hopefully, he will come to our rescue!

[Trebor76]

Assuming the data to removed is always the last bracketed data, this will do the job:

Option Explicit
Sub Macro2()

    'Written by Trebor76
    'Visit my website www.excelguru.net.au
    
    'Remove last bracketed contents as well as the brackets
    
    'http://www.excelforum.com/excel-programming-vba-macros/954524-macro-to-delete-certain-data-in-a-cell.html

    Dim intCellPos As Integer
    Dim rngCell As Range
    
    Application.ScreenUpdating = False
    
    For Each rngCell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
        For intCellPos = Len(rngCell) To 1 Step -1
            If Mid(rngCell, intCellPos, 1) = "(" Then
                intMyLen = intMyLen + 1
                rngCell = Left(rngCell, Len(rngCell) - intMyLen)
                rngCell = Application.WorksheetFunction.Substitute(rngCell, "(", "")
                rngCell = Application.WorksheetFunction.Substitute(rngCell, ")", "")
                intMyLen = 0
                Exit For
            Else
                intMyLen = intMyLen + 1
            End If
        Next intCellPos
    Next rngCell
    
    Application.ScreenUpdating = True
    
    MsgBox "Process is complete.", vbInformation, "Excel Guru"
    
End Sub

Regards,

Robert

[JBeaucaire]

Assuming:

1) Data is in column A starting at A2
2) The data in parens with a negative number in it are to be discarded
3) This parens group is always the last parens group



MANUAL TEST - try this, if this works for you on a larger data set, I can give this to you in a macro format:

B2: =LEFT(A2, SEARCH("(",A2)-1)
C2: =MID(A2, SEARCH("(", A2)+1, SEARCH(")", A2)-SEARCH("(", A2)-1)
D2: =B2&IF(ISNUMBER(SEARCH("-", C2)), "", " "& C2)

Copy B2:D2 down the whole dataset, the answers are appearing in D2.

[james1432]

Thank you JBeaucaire but Trebor76 macro is working like a charm for me.

[Winon]

@ Trebor76,

Thank you Robert, that is a very good macro.

@ JBeaucaire,

That is a very nice approach to avoid VBA. Thank you.

[Trebor76]

@ Winon

Pleasure. Thanks for the kind feedback

Robert

[HaHoBe]

Hi, Robert,

although Option Explicit is featured at the top I think this line is missing

Dim intMyLen As Integer

Or at least the mentioning that itīs a public variable

Ciao,
Holger

[Winon]

@ HaHoBe,

You are very alert today! LOL

However, I have tested Robert's Code and it works like a charm, regardless!

Regards

[Winon]

Hello james1432,

I am glad to hear that you could find use for my code somewhere else.

Also thank you for adding to my Reputation.

Baie dankie,

Groete

[Trebor76]

Hi james1432 and Winon,

Thank you both for your kind words and adding to my reputation

Regards,

Robert

[JBeaucaire]

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.