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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hi Yusuf

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

Best regards

Richard
 
Upvote 0
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.
 
Upvote 0
Try:

=IF(ISNUMBER(VALUE(LEFT(RIGHT(A2,3),1))),LEFT(A2,FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1))),A2)
 
Upvote 0
Awesome NBVC!!!

Not to trouble you too much but could you please give a brief explanation about the function SUBSTITUTE and @
 
Upvote 0
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)
 
Upvote 0
Thanks for the explanation and updated formula NBVC. They both work great.

Cheers
Yusuf
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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
Back
Top