# Splittin Postcodes into first part only with Left Keep

1. ## 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. ## Re: Splittin Postcodes into first part only with Left Keep

Something like this ?
=LEFT(SUBSTITUTE(A1," ",""),3)

3. ## 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:
`Please Login or Register  to view this content.`

4. ## 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. ## 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)

6. ## 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. ## 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.

##### Users Browsing this Thread

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

#### 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