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

what code have you got so far?

See if this UDF helps.

In a standard module

Function GetPostCodes(rng As Range)
    Dim strTemp As String, strResult As String
    Dim n As Long, r As Long
    Dim arrSplit As Variant
    
    strTemp = Replace(rng, "%", "")
    arrSplit = Split(strTemp, ",")
    
    For r = 0 To UBound(arrSplit)
        strTemp = arrSplit(r)
        For n = Len(strTemp) To 1 Step -1
            Select Case Mid(strTemp, n, 1)
                Case 0 To 9
                    strTemp = Left(strTemp, n - 1)
                Case Else
                    Exit For
            End Select
        Next
        For n = 1 To Len(strTemp)
            Select Case Left(strTemp, 1)
                Case 0 To 9
                    strTemp = Right(strTemp, Len(strTemp) - 1)
                Case Else
                    Exit For
            End Select
        Next
        strResult = strResult & strTemp & ", "
    Next
    If strResult = "" Then
        GetPostCodes = ""
    Else
        GetPostCodes = Left(strResult, Len(strResult) - 2)
    End If
End Function

Use in Excel like so ...
In say B2 Drag Down

Formula:

=GetPostCodes(A2)

Then in C2, Drag Across then Down

Formula:

=TRIM(MID(SUBSTITUTE(", "&$B2,", ",REPT(" ",255)),255*COLUMNS($C:C),255))

See this workbook

Formula:

=MID(LOOKUP("zzz",INDEX(LEFT(A1,ROW($1:$8)),0)),MIN(SEARCH({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},LOOKUP("zzz",INDEX(LEFT(A1,ROW($1:$8)),0))&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),LEN(LOOKUP("zzz",INDEX(LEFT(A1,ROW($1:$8)),0))))

or maybe just

Formula:

=MID(MID(A1,2,7),MIN(SEARCH({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},MID(A1,2,7)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),LEN(MID(A1,2,7)))

Another formula approach

Formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A2,FIND(LOOKUP(99^99,1*RIGHT(TRIM(A2),COLUMN($1:$1)))/10^5,A2)-1),"%00",""),"%0",""),"%","")

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.

Originally Posted by MULTIDROP

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.

Should work.

Note: the code only extracts valid post code(s).

i like that,,,,,,,,,,,,,,

@ Teethless mama

Your formula will not work for all cases.
UK Post codes can have from 5 to 7 characters, the last three are seperated from the rest by a space.

Consider these possibillities

String	                        Result	Actual Code
%M11AA15504002468914814826	M11AA	M1 1AA
%0WS13QE15504002468914814826	WS13QE	WS1 3QE
%0HU91TQ15504002468914814826	HU91TQ	HU9 1TQ
%0WS13QE15504002468914814826	WS13QE	WS1 3QE
%00EC1A1BB15504002468914814826	EC1A1BB	EC1A 1BB

See this workbook

using teethleess mama idea

Formula:

=left(substitute(substitute(substitute(left(a1,8),"%00",),"%0",),"%",),len(substitute(substitute(substitute(left(a1,8),"%00",),"%0",),"%",))-3)&" "&right(substitute(substitute(substitute(left(a1,8),"%00",),"%0",),"%",),3)

@ Martin

Your formula still seems to be restricted to 6 character codes
As I said in Post #12

UK Post codes can have from 5 to 7 characters, the last three are seperated from the rest by a space.

My suggestion in Post#7 handles all possible UK codes, provided there aren't invalid codes in the OPs data strings.

Formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A2,FIND(LOOKUP(99^99,1*RIGHT(TRIM(A2),COLUMN($1:$1)))/10^5,A2)-1),"%00",""),"%0",""),"%","")

With the formula in B2, this can then be used in C2 to partially validate the code and return the properly formatted code.

Formula:

=IF(AND(LEN(B2)>=5,LEN(B2)<=7,ISNUMBER(MID(B2,LEN(B2)-2,1)*1)),LEFT(B2,LEN(B2)-3)&" "&RIGHT(B2,3),"Invalid Code")

marcol i think the string always takes that into consideration hence the leading zeros for padding
so you wouldnt get
%00EC1A1BB15504002468914814826
but instead
%EC1A1BB15504002468914814826
and
%M11AA15504002468914814826
would be
%00M11AA15504002468914814826

@ Martin

i think the string always takes that into consideration hence the leading zeros for padding

Ahh ... I didn't think of that!

So if the code correctly needs the space then maybe

Formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A2,5),"%00",""),"%0",""),"%","")&" "&MID(A2,6,3)

If the space isn't required then Teethless mammas' suggestion suffices

Formula:

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

now we're getting there!

as long as you put say
%CA145DX15504002468914814826
%0WS13QE15504002468914814826
%0HU91TQ15504002468914814826
%0WS13QE15504002468914814826
%00L10AB15504002468914814826
into column a starting say a1 down
then in b1 put

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

fill the formula down

if they are in the format
%CA145DX15505382054963814826,%0WS13QE15505382055020814826,%0HU91TQ15505382069227814826,%0WS13QE15505382117853814826,%00L10AB15505382117853814826
copy the lot into word
find comma ,
replace with ^p
replace all
this will put them one below each other
then use the formula as above
(note 1 in open office writer find/replace - in more options -allow regular expressions -then find comma replace with \n )
(note 2 if you write the formula in open office calc then it's

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1;8);"%00";);"%0";);"%";)

because semi colons are used instead of commas however when you open in excel they are auto converted and vica-versa)

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?

nope that code will not run in open office,the formulas will.

i think i gave you the answer in post #20, please read all the responses and come back if there is something you don't understand