Extract text

[Blake 7]

Hi All - Good afternoon

I have a worksheet full of data (200,000) rows of which I have attached a small sample.

Col I (field name Town) contains some values with only two characters, these could be two letters, two numbers or a mixture.

I am trying to create a macro which removes ALL records (rows) if the value in col I contains only two chars.

Also, can anyone recommend the best way for me to obtain a unique list of all two chars from col I?

Cheers Guys

[zbor]

Here, try this:

Please Login or Register  to view this content.

[Blake 7]

Hi Z - will give it a bash now and let you know how it went. thanks for responding. hope all well with you.

[zbor]

Never better

[Blake 7]

never better!! lol wish i could say the same!! am pleased for you!

[zbor]

Hmm, yes... maybe I should better say:

I'm medium... Worse than yesterday, better than tomorrow

[ChemistB]

To get the unique list, I'd copy the entire column I somewhere, then (in 2007) delete duplicates, then in next column (let's say your unique list is in M), =LEN(M2)=2 and filter on false and delete those.

This is the macro I came up with to delete those rows

Please Login or Register  to view this content.

[Blake 7]

hi chem - hows it going. sorry for the delay in responding...i am currently running your code. There are in fact 375,656 records so its taking some time, 5 mins have passed already and screen still flashing!!!

will get back to you, cheers dude

[ChemistB]

Oops, probably should have put

Please Login or Register  to view this content.

into that code. That would speed things up and get rid of the flashing.

[Blake 7]

lol thanks Chem.

Thank you to both of you. both codes worked.

Cheers guys