MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Function needed to split the content of a cell into many cells

  Asked By: Alfonsa    Date: Feb 10    Category: MS Office    Views: 854

Iam trying to find some function that can split the content of a cell into
many cells.In short a function that does reverse process as that of
"Concatenate" function.



7 Answers Found

Answer #1    Answered By: Jet Brown     Answered On: Feb 10

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

Answer #2    Answered By: Ludkhannah Fischer     Answered On: Feb 10

To Separate First and Last Name from the List

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))

Answer #3    Answered By: Sairish Kauser     Answered On: Feb 10

.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....

Answer #4    Answered By: Javairea Akram     Answered On: Feb 10

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

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.

Answer #5    Answered By: Laura Rodriguez     Answered On: Feb 10

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

Answer #6    Answered By: Spiru Kelly     Answered On: Feb 10

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

Answer #7    Answered By: Jenny Lopez     Answered On: Feb 10

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