Data Cleansing- Separating 2 words

[nav505]

Hi, I have a column full of names of streets, I have separated the street names into a first word column and second word column, therefore, for the street name "Adam Road", the first word column contains the word "Adam", the second word column contains the word "Road",

I used the Left and Right formulas to separate the names into two columns e.g. =LEFT(A1,FIND(" ",A1,1)-1) so
"Adam" is placed in column B1, and =RIGHT(A1,LEN(A1)-FIND(" ",A1,1)) so Road is placed in column C2,

My question is for a street name such as "Greenhill", where there is no second word, using the same Left formula gives me the "#VALUE" error, is there a Left formula which can place all first words into a column, regardless of whether the street name contains both two words (e.g. Adam Road") or one word (e.g. Greenhill).

I would very much appreciate the help.

[benishiryo]

hi nav505, welcome to the forum. maybe:
=IF(ISNUMBER(FIND(" ",A1)),LEFT(A1,FIND(" ",A1)-1),A1)

[alansidman]

Try this:

If(isblank(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),"",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))

[nav505]

Thanks, that is wonderful

[nav505]

Also, is there a formula for the second word column that can give the second word of the street name (i.e.e Road for Adam Road) and also give a space when there is no second word in the street name (i.e. Greenhill)?

[alansidman]

I'm not understanding what you need. Please post and example of the data you have and the expected results so that we can have a clear picture of what you are looking to do.

[nav505]

I have data as follows,

STREET NAMES................FIRST WORD.............SECOND WORD
Aldbury Mews..................Aldbury....................Mews
Alden Avenue..................AldeN.......................Avenue
Alder Close......................Alder.......................Close
Aldermanbury...................Aldermanbury............#VALUE!
Aldermans Hill...................Aldermans.................Hill
Aldermans Walk................Aldermans..................Walk
Alderminster Road.............Alderminster...............Road


The previous formula that you posted can give the first word of all the street names, yet if there are no second words in the street name (i.e. Aldermanbury) then the SECOND WORD Column gives an error #VALUE, so I am asking whether there is a formula that can not only give the second word of the street name, but also give a space when there is no second word.

[alansidman]

In column B =IF(ISERROR(LEFT(A2,FIND(" ",A2,1)-1)),A2,LEFT(A2,FIND(" ",A2,1)-1))

In column C =IF(ISERR(RIGHT(A2,LEN(A2)-FIND(" ",A2,1))),"",RIGHT(A2,LEN(A2)-FIND(" ",A2,1)))