# 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

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.

#### SteveO59L

##### Well-known Member
Try Data, Text to Columns, with a space as the delimiter

#### Richard Schollar

##### MrExcel MVP
Hi Yusuf

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

Best regards

Richard

#### Yusuf

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

Try:

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

#### Yusuf

##### Active Member
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
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
Thanks for the explanation and updated formula NBVC. They both work great.

Cheers
Yusuf

Replies
8
Views
94
Replies
1
Views
54
Replies
2
Views
324
Replies
3
Views
103
Replies
10
Views
387