Deleting part of a cell

Hello all,

I am working on a spreadsheet at work that needs formatting. In the city column are listed all the cities (surprising I know) but they also have the county in there. I cannot use text to columns or anything that simple as the county names are all different lengths. I need to delete the county names from these cells. I would normally do it manually but there are around 1000 instances that need resolving!

Is there a formula that can do this for me? One that would delete the word on the righthand side of the cell would be ideal!

Many thanks

Rich

I'm assuming City, space, Country so something like this

=MID(A1,FIND(" ",A1,1)+1,99)

=LEFT(A1,FIND(" ",A1,1)-1)

Try this, but enter as an Array, Ctrl+Shift+Enter, in cell B1

=RIGHT(A1,LEN(A1)-MAX(ROW(INDIRECT("1:"&LEN(A1))) *(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")))

then in C1 enter this - standard formula

=LEFT(A1,LEN(A1)-LEN(B1)-1)