TextToColumns does this......................

To Separate First and Last Name from the List

Solution:
To retrieve the first name use the LEFT and FIND functions as shown in the
following formula:

=LEFT(A2,FIND(" ",A2))

To retrieve the last name use the RIGHT, LEN, and FIND functions as shown in
the following formula:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

.The solution can split  2
words......is there any function  that can do the exact reverse  function of
"concatenate" or that of text to column in the main menu.....I needed  a function
that can be used in the sheet as per my requirement....

Can you give us some examples of the content  of the original cell  and what you
want in the cells  as a result of the use of the function. Is it something
like:

Original cell A7 contains "Mary had a little lamb"

After application of function

Cell B7 Contains "Mary"
Cell C7 Contains "had"
Cell C8 Contains "a"
Cell C9 Contains "little"
Cell C10 Contains "lamb"

We need to understand what you need exactly. Then we may be able to help.

Try using the "text to columns" menu under data in excel. It will work I
already tried.

what you have
mentioned below is exactly what I require.The number of words may be more than
5 words......

What you need to use is TextToColumns.

You can select the source cells  (column A for example) and use data/text to
columns select delimited and space. and it will all be done for you.

If you need to use VBA for it it would look like:

Sub splitOutText()

Selection.TextToColumns DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, Space:=True

End Sub