Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Left Function and Text (2000 SR-1)

    I have to separate cities/towns from the post code and put them in separate columns for a huge mail merge, (around 1,000 addresses). There is something wrong in the formula I am using. Can any lounger help me out.

    This information came originally from another program which dumped the info into Excel.

    I have attached a spreadsheet with an example of the formula and the weird results that I am getting.

    Thanks in advance

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Left Function and Text (2000 SR-1)

    Hi Hetty,
    You can change your formula in column D to something like:
    <pre>=TRIM(MID(A1,B1+1,LEN(A1)))
    </pre>

    Your original RIGHT() formula counts from the right, not the left, which is why your formula was not working properly. I added the TRIM function as at least one of your entries has a double space between the Town and Postcode.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Left Function and Text (2000 SR-1)

    Rory

    You are the <img src=/S/king.gif border=0 alt=king width=21 height=22>

    Thank you so much. You're message came through just as I was threatening to strangle the person who dumped this mess on my desk.

Posting Permissions

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