none
Macro to identify postcodes and move it to another column RRS feed

  • Question

  • Hello, completely lost with a giant excel database. 

    I'm hoping sombody can help me by telling me if there's a way to identify a UK postcode and then have it moved to another column. 

    At the moment i have split the address string i originally recieved into 7 columns, but now the postcodes are all split up between the last 4 columns. I need a programe to be able to look through each column, identify a postcode and then move it to column 7.

    Example:

      A     B C     D E F G

    Name Address1 Address2 Address3 Address4 Address5 Postcode
    Joe Blogs 4 West Drive Newville Mersey CH99 9CH
    M. Mouse 3 East Way Merryland MM23 4NN
    D. Duck 1 Southpole Cold Verycold Freezing BR1 1BR
    John Smith 2 Northdrive Wetstone Cheadle Kent Scotland EC1W 5DH

    Any Help would be greately apreciated!

    Wednesday, November 4, 2015 5:11 PM

All replies

  • What is the format of UK post code ?
    Wednesday, November 4, 2015 5:17 PM
  • Hi, hope this helps! The following list shows all valid Postcode formats. "A" indicates an alphabetic character and "N" indicates a numeric character.

    FORMAT - EXAMPLE

    AN NAA - M1 1AA

    ANN NAA - M60 1NW

    AAN NAA - CR2 6XH

    AANN NAA - DN55 1PT

    ANA NAA - W1A 1HQ

    AANA NAA - EC1A 1BB


    Thursday, November 5, 2015 9:10 AM
  • Are they all upper case.  The easy way is to test for this

    If UCase(cell) = cell then
      ' is postal code
    Else
    '  not postal code
    End If
    You will need to loop through the 4 columns and all the rows.
    Thursday, November 5, 2015 1:08 PM
  • Maybe using RegEx
    Thursday, November 5, 2015 1:20 PM
  • Yes, they are all upper case.

     
    Thursday, November 5, 2015 1:38 PM