2

I have a table that has two Columns. Date and Test Name. What I would like to happen is that the string of text in one single cell be separated into multiple rows. In addition, I need the date to be associated with each set of text. I have tried text to columns and then transpose, but it can only handle 1 set of string at a time and not the entire data set.

enter image description here

Community
  • 1
  • 1
Sam
  • 75
  • 1
  • 1
  • 8
  • 8
    You will need vba. SO is not a code for me service. Also you have two other questions still open with answers. Did neither answer work for your question? If they answered the specific question you need to go back and mark them as correct, even if you realized you were asking the wrong question or decided to go a different route. – Scott Craner Feb 16 '16 at 17:32
  • 2
    Store into array then `split` via space? – findwindow Feb 16 '16 at 17:39
  • 3
    It looks like you need to reverse the code in [Aggregate, Collate and Transpose rows into columns](http://stackoverflow.com/questions/29440349/aggregate-collate-and-transpose-rows-into-columns). –  Feb 16 '16 at 17:41
  • @Jeeped - holy cow, thanks for that link/question. – BruceWayne Feb 16 '16 at 17:43
  • @ScottCraner Thank you Scott, I thought I had marked them correct, but I guess I did not. – Sam Feb 16 '16 at 18:52

3 Answers3

6

Loop through Column A then loop through the string next to it.

Results will be in column D

    Sub ChickatAH()
    Dim rng As Range, Lstrw As Long, c As Range
    Dim SpltRng As Range
    Dim i As Integer
    Dim Orig As Variant
    Dim txt As String

    Lstrw = Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = Range("A2:A" & Lstrw)

    For Each c In rng.Cells
        Set SpltRng = c.Offset(, 1)
        txt = SpltRng.Value
        Orig = Split(txt, " ")

        For i = 0 To UBound(Orig)
            Cells(Rows.Count, "D").End(xlUp).Offset(1) = c
            Cells(Rows.Count, "D").End(xlUp).Offset(, 1) = Orig(i)
        Next i

    Next c

End Sub
Davesexcel
  • 6,480
  • 2
  • 25
  • 41
  • This was exactly what i was looking for. I was only able to split the string for 1 range, but I couldn't figure out how to get it to match with the date. Thanks a bunch! – Sam Feb 17 '16 at 16:31
  • If i wanted to pull out more data correlating to Date and Test prior to split ( so if after test1 test 2 test3 in column c there is HIstory exam) and have that data be shown with date and test, would I just add a counter or a for loop? – Sam Feb 18 '16 at 15:56
  • I think I know what you mean, since this thread has already been answered, you can start a new thread with the new question. – Davesexcel Feb 18 '16 at 20:38
2

This will require a bit of copy and paste and also the use of WORD but here are a few steps that should help you out.

  1. Copy the cell in question.
  2. Open Word
  3. Paste Special (use the dropdown arrow below the paste icon)
  4. Select the option - Unformatted Unicode Text (as your paste special option)
  5. Select All
  6. Replace
  7. Find What: (type in the space) Replace With: ^p (creates a new line)
  8. Copy and paste results back into excel
Colin Stadig
  • 225
  • 2
  • 12
0

A formula solution is close to your requirement.

Image shown here.

Cell H1 is the delimiter. In this case a space.

Helper E1:=SUM(E1,LEN(B1)-LEN(SUBSTITUTE(B1,$H$1,"")))+1

You must fill the above formula one row more.

A8:=a1

Fill this formula to the right.

A9:=LOOKUP(ROW(1:1),$E:$E,A:A)

Fill this formula to the right and then down.

B9:=MID($H$1&LOOKUP(ROW(A1),E:E,B:B)&$H$1,FIND("艹",SUBSTITUTE($H$1&LOOKUP(ROW(A1),E:E,B:B)&$H$1,$H$1,"艹",ROW(A2)-LOOKUP(ROW(A1),E:E)))+1,FIND("艹",SUBSTITUTE($H$1&LOOKUP(ROW(A1),E:E,B:B)&$H$1,$H$1,"艹",ROW(A2)-LOOKUP(ROW(A1),E:E)+1))-FIND("艹",SUBSTITUTE($H$1&LOOKUP(ROW(A1),E:E,B:B)&$H$1,$H$1,"艹",ROW(A2)-LOOKUP(ROW(A1),E:E)))-1)

Fill down.

Bug:

Date/time will be converted to value and blank will be filled with 0. You can add &"" to the end of the formula of A9 and B9 to block the value 0, but numbers/date/time will be converted to text.

user3471272
  • 55
  • 6