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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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
 
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,129
Messages
5,857,537
Members
431,884
Latest member
Gcmoore63

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