parsing text return space to space

This formula will return the part string between the first and the second space in a text string.

=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",1)),FIND("|",SUBSTITUTE(A1," ","|",2))-FIND("|",SUBSTITUTE(A1," ","|",1)))

and you should be able to translate this into a VBA command.

Personally I would probably use the "Text to Column" method with space as a delimiter and take the substring from the second column.

Alf