# Returning a value

#### Yusuf

##### Active Member
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Try Data, Text to Columns, with a space as the delimiter

Hi Yusuf

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

Best regards

Richard

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.

Try:

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

Awesome NBVC!!!

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

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)

Thanks for the explanation and updated formula NBVC. They both work great.

Cheers
Yusuf

Replies
1
Views
264
Replies
8
Views
486
Replies
0
Views
428
Replies
1
Views
153
Replies
11
Views
803

1,217,257
Messages
6,135,503
Members
449,945
Latest member
noone12344444444

### 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.

### Which adblocker are you using?

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

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