Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Replace the first space (2K3)

    OK Friday conundrum <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    I know this should be easy

    In column E I have a set of post codes, badly formatted. The should look like this AANN NAA but some are coming out as AA NN NAA or AA N NAA where A=alpha and N= numeric

    Basically , how can I search the post code string and remove the space if and only if it appears at position 3 within the string and keep the space at either position 5 or 6 alone?
    Jerry

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Replace the first space (2K3)

    I was going to say "you can't," but how about =IF(MID(E2,3,1)=" ",LEFT(E2,2) & RIGHT(E2,LEN(E2)-3),E2)
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace the first space (2K3)

    This formula?

    =IF(FIND(" ",A1)=3,SUBSTITUTE(A1," ","",1),A1)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace the first space (2K3)

    With a postal code in E2:
    <code>
    =IF(MID(E2,3,1)=" ",SUBSTITUTE(E2," ","",1),E2)
    </code>
    Fill down as far as needed.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Replace the first space (2K3)

    Sammy/Pieterse/Hans

    Brilliant all <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

Posting Permissions

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