Splitting data in a cell in to multiple cells

[Excelrookie_1]

Hi There,

I need help in splitting the data in a cell in to multiple cells. Data needs to be split at the end of each line. Here's how the data currently in the cell that needs to be split

ABCDEFG
123456
LKOMNO
BHFRHIN
3frf4f
cfkvfvi0

All this is in one cell and i need it to be split in to different cells at the end of the each line in the same column.

Let me know if you require any addtional informaiton.

Regards

[:) Sixthsense :)]

Not clear about your expected output

[arlu1201]

Do you have multiple such data elements in your file or is it just one? Will there always be 6 entries in each cell or it can vary?

[Excelrookie_1]

Hi Arlette,

I just have one such data element as of now and the entires could vary from time to time we need to first determine how many lines are there in the cell first and split the data accordingly.

[nested.if1@gmail.com]

Hi,

Not sure, but the below might be helpful....


Assuming that you have Alt-Enter in those cells causing the data to
wrap, then you could highlight the column then click on Data | Text-to-
Columns. In the panel that pops up, click on Delimited, then Next,
then in the Other box enter CTRL-J (which is equivalent to Alt-Enter
in this case), then click Next, and then on the final panel you might
like to change the destination where the parsed columns will go, then
click Finish.

[arlu1201]

Did you try what is suggested in post 4?

[Excelrookie_1]

I tried but it didn't work. I need a macro to split the data as would required to do this step on a regular basis for a lot of files.

[arlu1201]

So you only the macro shortcut button to be assigned to that step of splitting the cell? Or do you want the macro to ask you the filename to be opened and then split the cell?

[kbkumar]

Assuming your data is in Column F2 and below of Sheet1, try this:

Sub DeLimit()
Dim R As Range
For Each R In Worksheets("Sheet1").Range("F2", Range("F" & Rows.Count()).End(xlUp))
R.TextToColumns _
      Destination:=R, _
      DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Tab:=True, _
      Semicolon:=False, _
      Comma:=False, _
      Space:=False, _
      Other:=True, _
      OtherChar:="" & Chr(10)
Next R
End Sub

You should be able to use this "regularly" on a particular workbook by changing the sheet name and column reference as per your needs. However, when you say "...for a lot of files", I think an ideal situation would be to save the macro as an Add-In...

Hope this helps.