Text Manipulation

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
581
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
=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,425
Office Version
  1. 2019
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
581
Office Version
  1. 2013
Platform
  1. Windows
Special-K99 the formula work but returns the Aberdeen not the post code
 

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
581
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Bump
 

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,676
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
581
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Thanks Sandeep worked fine
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,945
Office Version
  1. 2019
Platform
  1. 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
426
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,109
Messages
5,857,443
Members
431,879
Latest member
KiwDaWabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top