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

 

[purceld2]

Thanks all but for lesser mortals like me could someone explain how Bosco's formula works please.

 

[Sandeep Warrier]

Thanks all but for lesser mortals like me could someone explain how Bosco's formula works please.

TRIM(RIGHT(SUBSTITUTE( ," ",REPT()))) is a method to obtain the last word in a string.

For example: TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)) will replace all spaces in A1 with 255 spaces and return a string of 255 characters from the right. TRIM removes all excessive spaces and you are left with the last word.

Manipulating the numbers will allow you to get the last word, last two words etc.

In this case: TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),100))

This replaces all spaces with 50 spaces and returns 100 characters from the right. This is guaranteed to return the last 2 words with a whole load of spaces as long as each word is less than 25 characters (which is the case most of the time. If not, you can just increase the numbers. Note that the numbers within REPT and RIGHT have to multiples for this to work properly).

Example: In the tables given here the last two words in A1 is AB11 8HX. This function returns 8HX (3 characters) + 50 spaces + AB11 (4 characters) + 47 spaces (total 100 characters). TRIM then trims out excessive spaces and you are left with AB11 8HX.

Hope this helps.

 

[purceld2]

Sandeep thanks you very much for taking the time to explain the formula, very much appreciated

 

[YorkshireDave]

This works brilliantly for copying the data and I thank you all.
However, is it possible to cut and paste it?
I have over 2000 customers whos addresses are on a single line and so far its taken me two mind numbing hours to do just over 100!!!!

 

[Sandeep Warrier]

Hi & welcome to the Board!

What do you mean when you say

....
is it possible to cut and paste it?
I have over 2000 customers whos addresses are on a single line and so far its taken me two mind numbing hours to do just over 100!!!! ..

What is it you are trying to achieve?

 

[YorkshireDave]

Hi Sandeep.

Basically I have a very old database that I've converted to a CSV file. Unfortunately each of the fields contain too much information for me to use in a database. So I'm trying to fix it before importing it into a proper database.

So, the existing 'Name' field might contain a complete name, for example, 'Smith, Fred Mr'. What I need to do is somehow transfer (NOT copy) Smith into the LastName field, Fred into the FirstName field and Mr into a Salutation field.

I hope that makes sense.

 

[Sandeep Warrier]

Ok.

Is "Smith, Fred Mr" the only format or are there others? (i.e. In this case - the name starts with the last name, followed by a comma. The salutation is always the last word in the sentence. Everything between the comma and the salutation go in the first name column.)

 

[YorkshireDave]

Unfortunately no! What I'd like to understand Sandeep is how to construct a formula to 'pick out' certain elements of a cell's contents. Is that possible?:wink:

 

[Sandeep Warrier]

It certainly is possible. As long as you know what text you want extracted. There needs to be some identifiers eg. it occurs before/after some text/number/punctuation, or is x characters long, or is present in a list somewhere else etc.

Unless you define parameters this is not possible.

 

[YorkshireDave]

I guess the question now is 'how'.
From my example, how do I extract the 'Mr'?