Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    56

    Unanswered: OK, need helping with a VBA code in Excel for Postcodes Please (Sinndho, i need you)

    Hello again, its retarded jack, you know, the one you love so much and hardly ever hear from.

    OK heres whats happening, i have a new table, Fully populated with every postcode my database could possibly need, Nearly 200000 records.. all London and surrounding areas.

    now then, Simple little spreadsheet, 2 columns,

    Postcode - Boroughs

    If you see the attached excel spreadsheet, you will see what i mean,

    Now, as you scroll down the data, you notice that some postcodes look like this

    SE1 0AA
    SE1 0AB

    and some look like this:

    SE100AA
    SE100AA

    does anyone know of a little code that i can make to make excel filter through all of these postcodes and if they have no spaces in, make it automatically put the spaces in? otherwise im editting like 100000 records, because half of em are like it.. :-(

    Please help me people, im stuck..

    thanks

    much love, retarded jack..
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2011
    Posts
    56

    Thanks guys

    PANIC OVER!

    i used the find & Replace feature... -.-

    i know.. im a twat...or better known, retard..

    thanks

    retarded jack

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you may be able to insert a space before the last 3 digits of the code
    postcode = left(postcode,len(postcode)-3) & " " & right(postcode,3)
    but you would need to work out how to exclude postcodes which already have a space.
    the ideal solution would be to fix you data. whether thats in Access or Excel that'd be up to you

    in access VBA it would be something like
    update mytable
    set postcode = left(postcode,len(postcode)-3) & " " & right(postcode,3)
    where instr(postcode," ") = 0


    in excel it's be something simlar to
    = if(instr(postcodecellcolumn," ") = 0, left(postcodecellcolumn,len(postcodecellcolumn)-3) & " " & right(postcodecellcolumn,3), postcodecellcolumn)

    my excel is even rustier than Access VBA
    so you woudl need to check what the IF construct in Excel is
    postcodecellcolumn would be the reference to the cell containing the postcode
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •