Excel 2007 : IF Forumla to decifer digits other than letters and numbers

[trafficzombie]

Hi everyone

I am looking for help with an issue I have.

I have a file (1000's of lines) and one column is full of post codes and sometimes there is an issue with them, for example there is no space between them or a digit is missing or something other than a letter or number is in there.

I am wanting help on a code to scam the whole colum to find issues

Any help?

Thank in advance

[NBVC]

We are not all from the UK

It would be nice if you showed us an example dataset of what you have and what you need.

[trafficzombie]

Thaks for your help

Here is an example of a colum with letters and numbers (post code or zip code)

But as you will see some have no space and some have a full stop or any other symbol at the end.

this causes issues with a computer system I use to collate information and comes back with an error

When I have 1000's of them it is hard work to look through each one

M26 4GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M264GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD.
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M264GD
M26 4GD
M26 4GD.
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD/
M26 4GD
M26 4GD
M26 4GD
M26 4GD.
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD
M26 4GD

[NBVC]

Based on the samples, and assuming those are in column A starting at A1, then in another column apply formula:

=LEFT(TRIM(A1),3)&" "&MID(TRIM(SUBSTITUTE(A1," ","")),4,3)

copied down.

You can then copy and paste special|Values over the original column if desired.

[trafficzombie]

Based on the samples, and assuming those are in column A starting at A1, then in another column apply formula:

=LEFT(TRIM(A1),3)&" "&MID(TRIM(SUBSTITUTE(A1," ","")),4,3)

copied down.

You can then copy and paste special|Values over the original column if desired.

Excellent

That worked lovely apart from one thing I should have mentioned

Post codes can have anything between 5 and 7 digits, with the first part of the post code having anything from 2 to 4 digits, the 2nd part always being 3.



for example

M1 3ER
M26 4GD
TN10 3ER

So if we look at TN10 3ER* - with this code it changes it to TN1 03E

Your help is greatly appreciatted

[romperstomper]

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this, then please edit your first post to add links to all crossposts in other forums.

Thanks.

[NBVC]

Somehow, I figured it wasn't going to be that simple...

When posting samples please show all possibilities.. we can't guess!