Text Manipulation

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
549
I am trying to strip of the postcode /zip code and put in a cell on its own can someone advise the bust why to do this data below.

Excel Workbook
A
5Aberdeen AB11 8HX
6Acocks Green B27 7UW
7Acton W3 8HH
8Aldershot GU11 1JG
9Altrincham WA14 5PZ
10Ashford TN24 0SP
11Aylesbury HP20 1JH
12Banbury OX16 2RL
13Bangor LL57 4SU
14Basildon SS14 3GD
15Basingstoke RG21 8BJ
16Bath BA1 3AG
17Beckenham BR3 4TJ
18Bedford (Midland) MK40 1QH
19Bedford (St John's) MK42 0XE
20Bedminster BS3 3PZ
21Belshill ML4 1BA
22Birmingham (Perry Barr) B44 8AA
23Blackburn BB2 3DY
24Bolton BL2 1HE
25Bournemouth BH8 8BD
26Bradford BD5 7JL
27Braintree CM77 8YH
28Bridgwater TA6 4DB
29Brighton BN1 4GD
30Bristol (Downend) BS15 1SE
31Bristol (Filton) BS34 7JU
32Bristol (Newfoundland) BS2 9AP
33Burntwood (UK Autocare) WS7 3TQ
34Burton DE14 1SN
35Bury St Edmunds IP33 1XP
36Camberley GU15 3EJ
37Camborne TR15 3PS
38Cambridge (Clifton) CB1 7EA
summary


thank you in advance
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,344
=TRIM(LEFT(A2,LEN(A2)-8))
should work for most (W3 8HH) will present a problem

Postcodes will need to be 7 or 8 characters, if you just have something like SE1 it wont work
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,344
There could be a shorter formula but this will work perfectly assuming postcodes are seperated by space, ie "BN1 4GD" but not not "BN1"

=LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",SUM(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))/LEN(" ")-1))-1)
 

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
549
Special-K99 the formula work but returns the Aberdeen not the post code
 

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
549

ADVERTISEMENT

Bump
 

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
Hi

If the Zip Code is always the last 2 words then try
Excel Workbook
AB
1Aberdeen AB11 8HXAB11 8HX
2Acocks Green B27 7UWB27 7UW
3Acton W3 8HHW3 8HH
4Aldershot GU11 1JGGU11 1JG
5Altrincham WA14 5PZWA14 5PZ
6Ashford TN24 0SPTN24 0SP
7Aylesbury HP20 1JHHP20 1JH
8Banbury OX16 2RLOX16 2RL
9Bangor LL57 4SULL57 4SU
10Basildon SS14 3GDSS14 3GD
11Basingstoke RG21 8BJRG21 8BJ
12Bath BA1 3AGBA1 3AG
13Beckenham BR3 4TJBR3 4TJ
14Bedford (Midland) MK40 1QHMK40 1QH
15Bedford (St John's) MK42 0XEMK42 0XE
16Bedminster BS3 3PZBS3 3PZ
17Belshill ML4 1BAML4 1BA
18Birmingham (Perry Barr) B44 8AAB44 8AA
19Blackburn BB2 3DYBB2 3DY
20Bolton BL2 1HEBL2 1HE
21Bournemouth BH8 8BDBH8 8BD
22Bradford BD5 7JLBD5 7JL
23Braintree CM77 8YHCM77 8YH
24Bridgwater TA6 4DBTA6 4DB
25Brighton BN1 4GDBN1 4GD
26Bristol (Downend) BS15 1SEBS15 1SE
27Bristol (Filton) BS34 7JUBS34 7JU
28Bristol (Newfoundland) BS2 9APBS2 9AP
29Burntwood (UK Autocare) WS7 3TQWS7 3TQ
30Burton DE14 1SNDE14 1SN
Sheet1
Excel 2003
Cell Formulas
RangeFormula
B1=REPLACE(A1,1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)),"")
 

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
549

ADVERTISEMENT

Thanks Sandeep worked fine
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
2019
Platform
Windows
Using Sandeep's table, B1 formula copy down :

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),100))

Regards
Bosco
 

vinod9111

Active Member
Joined
Jan 21, 2009
Messages
425
Excellent Bosco,

What an elegant formula, wonderful part of excel how one interpret the problem and get a simple soloution

Sandeep your formula is also good.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,597
Messages
5,512,313
Members
408,886
Latest member
kashifziatevta423

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top