Text Manipulation

[purceld2]

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

thank you in advance

 

[Special-K99]

=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

 

[Special-K99]

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]

Special-K99 the formula work but returns the Aberdeen not the post code

 

[purceld2]

Bump

 

[Sandeep Warrier]

Hi

If the Zip Code is always the last 2 words then try

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

Excel 2003

Cell Formulas
Range	Formula
B1	=REPLACE(A1,1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)),"")

 

[purceld2]

Thanks Sandeep worked fine

 

[bosco_yip]

Using Sandeep's table, B1 formula copy down :

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

Regards
Bosco

 

[Sandeep Warrier]

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

Nice!!!

 

[vinod9111]

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.