Returning a value

Yusuf

Active Member
Joined
Jun 1, 2004
Messages
337
Hi

I have a column with cells containing City names and Post codes together and need to split them to return only the City name.
Should the City name be there alone, then it should be returned as is.

Eg.
A1 - London S7 2GY
A2 - Crawley CS4 9JK
A3 - Edinburgh EH26 8NF

The length of the postcode could change depending on the area.

Any help with this would be greatly appreciated.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Yusuf

Possibly going Data>TextToColumns and using a space delimiter will achieve what you want.

Best regards

Richard
 

Yusuf

Active Member
Joined
Jun 1, 2004
Messages
337
Thanks guys

My apologies for not giving better examples

Sometimes I have city names like "South End On Sea" or "Edinburgh West" etc

This is what I'm using at the moment;
=IF(ISNUMBER(VALUE(LEFT(RIGHT(A2,3),1))),LEFT(A2,LEN(A2) - 8),A2)

Problem is that when the postcode is less than 8 (which happens to be the most digits) it chops the last letter or two from the town name but otherwise works great.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

Try:

=IF(ISNUMBER(VALUE(LEFT(RIGHT(A2,3),1))),LEFT(A2,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))),A2)
 

Yusuf

Active Member
Joined
Jun 1, 2004
Messages
337
Awesome NBVC!!!

Not to trouble you too much but could you please give a brief explanation about the function SUBSTITUTE and @
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Basically, this part of the function finds the location of the second-to-last space in the string:

FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))


The "@" is used as a temporary placeholder and it is inserted via the Substitute function in the place of interest.


The following part of the function calculates the number of spaces in the string: LEN(A2)-LEN(SUBSTITUTE(A2," ","") by taking the length of the original string and subtracting the length of the string after all spaces have been substituted with nulls.

The main Substitute function then replaces the second-to-last space (determined by subtracting 1 from the total number of spaces found with the previously described substitute function) with an "@".

Then the FIND() function locates the position of that "@" and uses that to determine how many characters to take in the main LEFT() function.

Actually, come to think of it, I did neglect to subtract another 1 character. The formula I gave you actually retains the space at the end...use the following revision instead.

=IF(ISNUMBER(VALUE(LEFT(RIGHT(A2,3),1))),LEFT(A2,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))-1),A2)
 

Yusuf

Active Member
Joined
Jun 1, 2004
Messages
337
Thanks for the explanation and updated formula NBVC. They both work great.

Cheers
Yusuf
 

Watch MrExcel Video

Forum statistics

Threads
1,114,676
Messages
5,549,371
Members
410,911
Latest member
AniEx
Top