Formulars in Excel 2003

QuestionEdit

Hi Bob I do a bit of work in Excel 2003 where I have to split text from 1 cell to 2 cells . For instance "Johnstone Street" to "Johnstone" "Street". I normaly do this using a formula containing (find left right etc) The problem I have is when I have a name like "Johnstone West Street" it puts "Johnstone" in the first column & "West Street" in the other column. Is it possible to write a formula that will put the Street name/s in the fist column & the street type in the second column and for this formular to recognise if the street name is 1 or 2 words and take appropiate action? Would this be better in a Macro?

AnswerEdit

There's no way to know if AAA BBBB CCCCC should be split like AAA BBBB CCCCC or AAA BBBB CCCCC

(Excel can't know if text is a city or not!)

Excel DOES provide a builtin way to split this stuff across columns, but it there are 3 words, it'll take up 3 columns. Data/Text-to-Columns, select Delimited, then click the Space checkbox & click finish