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.

[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.

Please Login or Register  to view this content.

[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..

[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,

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:

Please Login or Register  to view this content.

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.

[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

Please Login or Register  to view this content.

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

[james1432]

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

[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.