I have an excel file that contains 4 different data items in a single column separated by a <space>. I need to write a subroutine that separates the string of characters in the cell based on the positions of the <spaces>.
Example string "France Adam Down Holiday"
Dont think there is a combination of spreadsheet excel functions that will do this. Therefore will have to write some visual basic code.
Need some help here if possible in a little bit of code.
Im thinking a combination of "Instr" and "Left" VB functions will do this for me. Is there a simpler way of splitting the string using other string manipulation functions?

Any help is much appreciated.

Why not just use Data>Text to columns...?

If you really need code then you could use the macro recorder when you do that manually.

I agree with Norie on this. However, if you want a couple of formulas, then for the

first word of the cell (A28) use:

=IF(ISERR(LEFT(A28,FIND(" ",A28)-1)),A28,LEFT(A28,FIND(" ",A28)-1))

Last word of the cell (A28) use:

=IF(LEN(A28)-LEN(SUBSTITUTE(A28," ",""))=0,A28,RIGHT(A28,LEN(A28)-FIND("*",SUBSTITUTE(A28," ","*",LEN(A28)-LEN(SUBSTITUTE(A28," ",""))))))

To get the third word, it is two steps:

This gives you the first three words (put this in cell F28):

=LEFT(A28,LEN(A28)-LEN(IF(LEN(A28)-LEN(SUBSTITUTE(A28," ",""))=0,A28,RIGHT(A28,LEN(A28)-FIND("*",SUBSTITUTE(A28," ","*",LEN(A28)-LEN(SUBSTITUTE(A28," ","")))))))-1)

Then this gives you the third word, by building on the above formula (you can't combine, because it goes beyond 1024 characters) (put in cell G28)

=IF(LEN(F28)-LEN(SUBSTITUTE(F28," ",""))=0,F28,RIGHT(F28,LEN(F28)-FIND("*",SUBSTITUTE(F28," ","*",LEN(F28)-LEN(SUBSTITUTE(F28," ",""))))))

Given this, you should be able to extract the second word.

Thanks for the help. Was just looking for the easy way to do it and the text to columns worked for me .