Delete unwanted data in a range of cells

[TheGowser]

Hi there,

I hope someone can help with my query....
Basically I require some unwanted information contained in a range of cells deleting.

I have attached a file with 2 sheets...a before and after. I've been through the 'Text to columns' process, but in this instance it doesn't work.

Any help would be greatly appreciated.

Regards,
Graham.

[alansidman]

Power Query

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[BadlySpelledBuoy]

Another option would be running the below macro.

Please Login or Register  to view this content.

BSB

[TheGowser]

Well, thankyou very much Alan for your quick response.
I'm not sure where I put the code you attached, but I'll try to have a go with it....

[BadlySpelledBuoy]

Click the button top left of sheet1 to run the code mentioned in post#3.

BSB

[TheGowser]

Thankyou BSB. There seems to be an error with the Me.UsedRange
I am not an expert with this code stuff....so its probably me....

[TheGowser]

BSB that is Perfecto!!! Thankyou very much indeed!

[alansidman]

Instructions are in the post as to where and how to use the Mcode.

[BadlySpelledBuoy]

BSB that is Perfecto!!! Thankyou very much indeed!

Happy to help.

I would suggest if you're going to learn one way of doing this that you go for Alan's approach as it's the way forward. VBA will die a death at some point in the future.

BSB

[TheGowser]

missed that Alan, thankyou very much for your help