Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Extract Part Text from cell (Excell 2002)

    Hi

    I need to split hundreds of UK post codes, for example ML1 4RU would be ML, G3 3XP would be G and W1 AV would be W1

    ML1 4RU = ML
    G3 3XP = G
    W1 AV = W1

    The second two codes are the problem they both start with two alphanumeric although part two of the codes differ one ends with 3 and the other ends with 2
    any help would be appreciated. these are the only three formats

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extract Part Text from cell (Excell 2002)

    With only these 3 examples to go with (I know nothing about UK post codes) how does this do:

    =IF(LEFT(A1,2)="G3","G",LEFT(A1,2))

    If not correct, could you provide more "clues" to the puzzle or more examples.(Perhaps where the above fails)

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Part Text from cell (Excell 2002)

    Hi Steve

    That seem's to work on the samples I gave, I will try it on others and get back to you if I require more help.

    Many Thanks Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Part Text from cell (Excell 2002)

    Braddy,

    Steve's formula will only work for UK post codes beginning with any 2 letters or those beginning exactly "G3". The following will catch all the UK post code styles I know of:

    =IF(FIND(" ",A5,1)=3,LEFT(A5,1),IF(OR(FIND(" ",A5,1)=4,FIND(" ",A5,1)=5),LEFT(A5,2),"not found"))

    stuck

Posting Permissions

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