Extracting UK postcodes from string of text to import into Microsoft Autoroute.

Hi, I'm new to this so apologies if this is the wrong place to ask this question.

I get data emailed to me that is just a string of text, comma separated I think, that I need to extract the UK 5, 6 or 7 digit postcode from.

An example of the text can be seen below:

%CA145DX15505382054963814826,%0WS13QE15505382055020814826,%0HU91TQ15505382069227814826,%0WS13QE15505382117853814826,%00L10AB15505382117853814826,

or sometimes it comes like this:

%CA145DX15504002468914814826
%0WS13QE15504002468914814826
%0HU91TQ15504002468914814826
%0WS13QE15504002468914814826
%00L10AB15504002468914814826

Ideally, i would like to set up a template, that had some special macros or VBA or whatever you call it, that would ignore the leading % and 1 or 2 zeros and the rest of the numbers after the postcode and just extract the 5, 6 or 7 digit postcode so I can import them into Microsoft Autoroute to use the locations.

Is this possible?

The postcodes are highlighted in bold to make it easier to see them.

Thanks in advance for your help.

Al.

You've got me there...!

I have searched round the net for an answer, seen that other people have had a similar problem, with postcode data mixed up with other stuff.

I HAVE NO IDEA how to write code, writing a simple formula in a spreadsheet is the limit of my abilities, but I am willing to learn.

Thanks for your quick response.

Microsoft Autoroute is called Streets and Trips in the US.

Thanks,

Al.

This seems to work

Please Login or Register  to view this content.

Thanks all, will give this a go when I get back home.

Not sure what I do with the formula, but if I open the attached .xlsm files, and paste some more data, should that work?

Al.

Originally Posted by Marcol

Another formula approach

Formula:

Please Login or Register  to view this content.

Use Text to Colums to split cells with comma seperated strings.
Then Copy the result > Paste Special > Transpose (Check the Transpose box)

Refer the formula to the first cell, then drag down.

Shorter version:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A2,8),"%00",),"%0",),"%",)

Updated version (output)

Hi again, and thanks to you all for taking time over working this out.

If it helps, when I import data into Microsoft Autoroute to map the locations, it doesn't matter if the postcodes have the gap between the first 2, 3 or 4 digits and the last 3 digits, it recognises them as UK postcodes as long as they are in the same cell in a spreadsheet.

Ideally the "extraction" would leave the extracted postcodes in a new column so i can cut and paste that into a new blank spreadsheet and import that data into MS Autoroute. It lets me know if any of the postcodes are not recognised as valid UK ones at that point, and I can make a note and validate them manually.
(this only really occurs with new build properties that aren't in the Autoroute data base yet).

Could someone, really simply, step by step talk me through how I get your solutions to work, set up a new spreadsheet, which cells to paste the formula in etc, and can they be used with Open Office as well?

Thanks once again.

Al.

Just paste the data in col.A of Sheet1, it will output the result in sheet2 when button is clicked.

Open office?

Forget about my codes.

The solutions to your problem have already been posted to this thread.

You say in Post #1

An example of the text can be seen below:

%CA145DX15505382054963814826,%0WS13QE15505382055020814826,%0HU91TQ15505382069227814826,%0WS13QE15505382117853814826,%00L10AB15505382117853814826,

or sometimes it comes like this:

%CA145DX15504002468914814826
%0WS13QE15504002468914814826
%0HU91TQ15504002468914814826
%0WS13QE15504002468914814826
%00L10AB15504002468914814826

How about you post a typical workbook showing exactly what you import to Excel?

That way someone can perhaps summarise this thread and return your workbook as a template.

OK, here is an example, I just copy and paste the text from an email into Excel 2003.

Hope this gives you some idea, there can be up to 100 postcodes to extract in one go.

Thanks once again for your time and effort.

Al.

Yes, thanks, your first formula in post 20 works just great.

In Excel and Open Office.

Thanks for your help

Al.