+ Reply to Thread
Results 1 to 7 of 7

Splittin Postcodes into first part only with Left Keep

  1. #1
    Registered User
    Join Date
    04-29-2014
    Location
    Manchester
    MS-Off Ver
    Excel 2010
    Posts
    22

    Splittin Postcodes into first part only with Left Keep

    I have a spreadsheet which is 150,000 rows long which contains postcodes. I have used a left keep =left(A1,4) to split out the first part only, this works fine for any postcode where the first part is 3 or 4 characters long IE DY6 or DY14. Some of the data unfortunatley has no spaces within the post codes IE DY53FL which using the formula keeps DY53 when i want it to keep only DY5.
    EG
    DY5 4TD DY5
    DY5 4TN DY5
    DY53FL DY53
    DY53NN DY53

    Is there an easier way to do this other than scrolling down and either adding a space to the cells with no space or changing the formula to =left(A1,3)

    I had thought maybe there is a way to sort the list by number of characters not including spaces which means i could then add the Left3 & Left4 easier to a single block of data.

    EG


    I hope this makes sense, the reason needed is to put the data into Tableau with the first part of the post code only.

    Thanks

  2. #2
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Splittin Postcodes into first part only with Left Keep

    Something like this ?
    =LEFT(SUBSTITUTE(A1," ",""),3)
    Click just below left if it helps, Booşathì

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,638

    Re: Splittin Postcodes into first part only with Left Keep

    Hi,

    Since the inward part of the code only contains one numeric then you can use

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    04-29-2014
    Location
    Manchester
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Splittin Postcodes into first part only with Left Keep

    Boopathi - Thanks for the suggesstion.
    That works to an extent with the cells which are 6 characters with no spaces, IE E152RZ BUT does not return enough characters on 7 digit AA10 1AA or 5 digit A11AA. Therefore as the list cannot be sorted to group all the issues in blocks it still involves me scrolling through the list manually and changing the formula.

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Splittin Postcodes into first part only with Left Keep

    As the second part is always 3 characters you should be able to take out spaces then remove the last 3 characters, i.e.

    =LEFT(A1,LEN(SUBSTITUTE(A1," ",""))-3)
    Audere est facere

  6. #6
    Registered User
    Join Date
    04-29-2014
    Location
    Manchester
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Splittin Postcodes into first part only with Left Keep

    Richard - This seems to have worked a treat, not sure exactly how this works but it does, ive tried to decipher the formula to understand it but cannot.

    Thank you so much.

  7. #7
    Registered User
    Join Date
    04-29-2014
    Location
    Manchester
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Splittin Postcodes into first part only with Left Keep

    DaddyLongLegs - Thank you.

    This also works and is something i can get my head round.
    Last edited by kelvin156; 09-24-2014 at 09:06 AM.

+ 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. Extract text left and right of character part 2
    By stuartm4h in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-01-2013, 07:12 AM
  2. [SOLVED] 2 parts - Part 1 - Add an X to both the left and the right on a column of 250 numbers
    By ILoveStMartin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-26-2012, 07:16 AM
  3. VLOOKUP syntax for a both whole and part postcodes
    By NatashaBatsford in forum Excel General
    Replies: 3
    Last Post: 04-28-2010, 03:50 AM
  4. [SOLVED] Slect left and right part of text
    By Soniya in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-22-2006, 05:55 AM
  5. [SOLVED] return left most part of cell
    By Pat in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-09-2005, 12:05 PM

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