+ Reply to Thread
Results 1 to 3 of 3

Help needed to amend the format of certain Post Codes

  1. #1
    Registered User
    Join Date
    09-17-2014
    Location
    Cambridge, England
    MS-Off Ver
    2016
    Posts
    58

    Help needed to amend the format of certain Post Codes

    Morning,

    And happy christmas,

    I need some help. I have inherited a report from a previous Analyst. It is basically a complete system extract for a specific insurance company we deal with. Our extract has lots of formulas to amend the format of the columns as the entire Excel extract is then saved in Text format as the specific insurer needs it in Text so they can fit it into their own template their end. It is the length of certain postcodes where we are having an issue. Via Cognos my queries give me the Excel extract with no issues. However the spreadsheet I have inherited uses the following formula =IF(H3="","",SUBSTITUTE('Cognos Report'!I2," "," ")&REPT(" ",8-LEN(SUBSTITUTE('Cognos Report'!I2," "," ")))) to populate the Text template with the Post Code. Currently this works fine for the formatting and leaves a space in between the two blocks of postcodes. So CB6 2WU shows correctly on the Template as CB6 3WU. However the insurer has now requested that for London post codes (where there are only 5 digits - W1 5ST for example) they want a 2nd space in between the two blocks of digits, yet postcodes with more than 5 digits need to remain as they are. This is because their template is falling over with a single space between them.

    Hopefully that is as clear as mud. I would prefer not to mess around with the existing formula too much. But if anyone can think of a better logic to it, or an amendment that will do the trick - I would be very happy.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Help needed to amend the format of certain Post Codes

    Not clear to me... can you post representative examples of what you are starting with.
    Glenn



  3. #3
    Forum Contributor
    Join Date
    03-17-2005
    Location
    Canterbury, Kent
    MS-Off Ver
    2003, 2010
    Posts
    285

    Re: Help needed to amend the format of certain Post Codes

    Could you put in another IF statement that checks the number of characters in the cell, if less than 7 then substitute " " for " "

    Used 7 as the outside London post code has 7 characters.

    Windy

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help needed in changes codes
    By shido in forum Excel General
    Replies: 2
    Last Post: 12-06-2014, 05:27 PM
  2. Two VBA codes needed to paste in same unused row
    By gsot21 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-14-2014, 01:43 PM
  3. Custom format UK telephone numbers/UK post codes
    By Ju1cy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-13-2013, 07:00 AM
  4. Post Codes
    By excel_lover in forum Excel General
    Replies: 5
    Last Post: 09-11-2008, 08:09 PM
  5. re : Help needed on some codes
    By dic09 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-11-2006, 01:26 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1