Split Cells with Alt-Enter to Rows

L

lrgalvez2

New Member
Joined
Jul 7, 2015
Messages
5
Hi! Can anyone help me please? I have data where the last 3 Columns have multiple data in each cell using Alt+Enter. I need to split the multiple data into one row for each data and for each row the first column is copied. Please see below:

table.jpg

​

I have used the Code submitted by Apo here in this thread and it works great for the Column "Types" but I need to perform it in two other columns.

I have thousands of records and any help will be appreciated.

Thanks
 
Sort by date Sort by votes
lrgalvez2,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

Here is a macro solution for you to consider that is based on the graphic/picture you have displayed.

You can changed the raw data worksheet name in the macro.

Sample raw data in worksheet Sheet1:


Excel 2007
ABCD
1TestTypesScoresSubscore
2Math Test1 2 370 20 3623 4 66
3English Test4 510 1512 99
4Reading Test6 755 8812 76
5
6
7
8
9
Sheet1


After the macro:


Excel 2007
ABCD
1TestTypesScoresSubscore
2Math Test17023
3Math Test2204
4Math Test33666
5English Test41012
6English Test51599
7Reading Test65512
8Reading Test78876
9
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub ReorgSplitData()
' hiker95, 07/07/2015, ME866439
Dim r As Long, lr As Long, s
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For r = lr To 2 Step -1
    If InStr(.Cells(r, 2), vbLf) Then
      s = Split(.Cells(r, 2), vbLf)
      .Rows(r + 1).Resize(UBound(s)).Insert
      .Cells(r + 1, 1).Resize(UBound(s)) = .Cells(r, 1)
      .Cells(r, 2).Resize(UBound(s) + 1) = Application.Transpose(s)
      s = Split(.Cells(r, 3), vbLf)
      .Cells(r, 3).Resize(UBound(s) + 1) = Application.Transpose(s)
      s = Split(.Cells(r, 4), vbLf)
      .Cells(r, 4).Resize(UBound(s) + 1) = Application.Transpose(s)
    End If
  Next r
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgSplitData macro.
 
Upvote 0
lrgalvez2,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0
Hello Hiker it's me again. The Code you gave works if the cells that need to be split are found in Column B - D. I have cases where the cells in columns A - F are single line cells (Like Column A in my example) then columns G - I are multi-line cells (Like Columns B-D of example) and then again Columns J - K are single line cells. Below is sample:


table2.jpg


Can you please provide me with a code that works with the above example? I tried to do it myself but it's not as easy as it seems. I'm a complete beginner when it comes to VB.

Thanks in advance.
 
Upvote 0
lrgalvez2,

You are posting a much larger picture/graphic again. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense and I doubt that you would get any answer.

In order to continue you will have to supply actual screenshots that I can use for testing:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, flat text) try one of the following:

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


The following is a free site:

You can upload your workbook to (the BLUE link-->) Box Net ,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.
 
Upvote 0
Here's a sample of the actual data:

Excel 2012

ABCDEFGHIJKLMN


<tbody>
[TD="align: center"] 1 [/TD]
[TD="bgcolor: #4F81BD"] From This: [/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]

[TD="align: center"] 2 [/TD]
[TD="bgcolor: #4F81BD, align: center"] ID [/TD]
[TD="bgcolor: #4F81BD, align: center"] First [/TD]
[TD="bgcolor: #4F81BD, align: center"] Last [/TD]
[TD="bgcolor: #4F81BD, align: center"] Email [/TD]
[TD="bgcolor: #4F81BD, align: center"] Date [/TD]
[TD="bgcolor: #4F81BD, align: center"] Library [/TD]
[TD="bgcolor: #4F81BD, align: center"] Test(s) [/TD]
[TD="bgcolor: #4F81BD, align: center"] Score [/TD]
[TD="bgcolor: #4F81BD, align: center"] Percentile [/TD]
[TD="bgcolor: #4F81BD, align: center"] Comp [/TD]
[TD="bgcolor: #4F81BD, align: center"] Industry [/TD]
[TD="bgcolor: #4F81BD, align: center"] % Range [/TD]
[TD="bgcolor: #4F81BD, align: center"] Competency [/TD]
[TD="bgcolor: #4F81BD, align: center"] As of [/TD]

[TD="align: center"] 3 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 1091 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] Juan [/TD]
[TD="bgcolor: #DAEEF3, align: center"] Cruz [/TD]
[TD="bgcolor: #DAEEF3, align: center"] juandelacruz@gmail.com [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 2/2/10 [/TD]
[TD="bgcolor: #DAEEF3, align: center"]
[/TD]
[TD="bgcolor: #DAEEF3, align: center"] Math [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 24.00 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 80 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] ABC [/TD]
[TD="bgcolor: #DAEEF3, align: center"] Food [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 71-100 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] Multiple [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 7/9/15 [/TD]

[TD="align: center"] 4 [/TD]
[TD="align: center"] 1092 [/TD]
[TD="align: center"] John [/TD]
[TD="align: center"] Doe [/TD]
[TD="align: center"] john.doe@yahoo.com [/TD]
[TD="align: center"] 12/9/11 [/TD]
[TD="align: center"] Battery 1 [/TD]
[TD="align: center"] English
Verbal
Comprehension [/TD]
[TD="align: center"] 90.0
2.88
Multi [/TD]
[TD="align: center"] 73.0
53.0
Multi [/TD]
[TD="align: center"] XYZ [/TD]
[TD="align: center"] Car [/TD]
[TD="align: center"] FALSE [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] 7/9/15 [/TD]

[TD="align: center"] 5 [/TD]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]

[TD="align: center"] 6 [/TD]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]

[TD="align: center"] 7 [/TD]
[TD="bgcolor: #4F81BD"] To This: [/TD]

[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]

[TD="align: center"] 8 [/TD]
[TD="bgcolor: #4F81BD, align: center"] ID [/TD]
[TD="bgcolor: #4F81BD, align: center"] First [/TD]
[TD="bgcolor: #4F81BD, align: center"] Last [/TD]
[TD="bgcolor: #4F81BD, align: center"] Email [/TD]
[TD="bgcolor: #4F81BD, align: center"] Date [/TD]
[TD="bgcolor: #4F81BD, align: center"] Library [/TD]
[TD="bgcolor: #4F81BD, align: center"] Test(s) [/TD]
[TD="bgcolor: #4F81BD, align: center"] Score [/TD]
[TD="bgcolor: #4F81BD, align: center"] Percentile [/TD]
[TD="bgcolor: #4F81BD, align: center"] Comp [/TD]
[TD="bgcolor: #4F81BD, align: center"] Industry [/TD]
[TD="bgcolor: #4F81BD, align: center"] % Range [/TD]
[TD="bgcolor: #4F81BD, align: center"] Competency [/TD]
[TD="bgcolor: #4F81BD, align: center"] As of [/TD]

[TD="align: center"] 9 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 1091 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] Juan [/TD]
[TD="bgcolor: #DAEEF3, align: center"] Cruz [/TD]
[TD="bgcolor: #DAEEF3"] juandelacruz@gmail.com [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 2/2/10 [/TD]
[TD="bgcolor: #DAEEF3, align: right"]
[/TD]
[TD="bgcolor: #DAEEF3, align: center"] Math [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 24.00 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 80 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] ABC [/TD]
[TD="bgcolor: #DAEEF3, align: center"] Food [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 71-100 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] Multiple [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 7/9/15 [/TD]

[TD="align: center"] 10 [/TD]
[TD="align: center"] 1092 [/TD]
[TD="align: center"] John [/TD]
[TD="align: center"] Doe [/TD]
[TD="align: center"] john.doe@yahoo.com [/TD]
[TD="align: center"] 12/9/11 [/TD]
[TD="align: center"] Battery 1 [/TD]
[TD="align: center"] English [/TD]
[TD="align: center"] 90 [/TD]
[TD="align: center"] 73 [/TD]
[TD="align: center"] XYZ [/TD]
[TD="align: center"] Car [/TD]
[TD="align: center"] 71-100 [/TD]
[TD="align: center"] Single [/TD]
[TD="align: center"] 7/9/15 [/TD]

[TD="align: center"] 11 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 1092 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] John [/TD]
[TD="bgcolor: #DAEEF3, align: center"] Doe [/TD]
[TD="bgcolor: #DAEEF3, align: center"] john.doe@yahoo.com [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 12/9/11 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] Battery 1 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] Verbal [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 2.88 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 53 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] XYZ [/TD]
[TD="bgcolor: #DAEEF3, align: center"] Car [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 31-70 [/TD]
[TD="bgcolor: #DAEEF3, align: center"] Single [/TD]
[TD="bgcolor: #DAEEF3, align: center"] 7/9/15 [/TD]

[TD="align: center"] 12 [/TD]
[TD="align: center"] 1092 [/TD]
[TD="align: center"] John [/TD]
[TD="align: center"] Doe [/TD]
[TD="align: center"] john.doe@yahoo.com [/TD]
[TD="align: center"] 12/9/11 [/TD]
[TD="align: center"] Battery 1 [/TD]
[TD="align: center"] Comprehension [/TD]
[TD="align: center"] Multi [/TD]
[TD="align: center"] Multi [/TD]
[TD="align: center"] XYZ [/TD]
[TD="align: center"] Car [/TD]
[TD="align: center"] FALSE [/TD]
[TD="align: center"] Multiple [/TD]
[TD="align: center"] 7/9/15 [/TD]

</tbody>
Sheet1​


[TABLE="width: 1074"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 1066"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]L3[/TH]
[TD="align: left"]=IF(I3<=30,"0-30",IF((AND(I3>30,I3<=70)),"31-70",IF((AND(I3>70,I3<=100)),"71-100")))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]L9[/TH]
[TD="align: left"]=IF(I9<=30,"0-30",IF((AND(I9>30,I9<=70)),"31-70",IF((AND(I9>70,I9<=100)),"71-100")))

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


I used MrExcelHtml. Great tool! Thanks for pointing it out to me.
 
Upvote 0
lrgalvez2,

I used MrExcelHtml. Great tool! Thanks for pointing it out to me.
Click to expand...

You are very welcome.



Excel 2007
ABCGHIMN
1IDFirstLastTest(s)ScorePercentileCompetencyAs of
21091JuanCruzMath2480Multiple7/9/2015
31092JohnDoeEnglish Comprehension Verbal90 2.88 Multi73 53 Multi7/9/2015
4
5
6
7IDFirstLastTest(s)ScorePercentileCompetencyAs of
81091JuanCruzMath2480Multiple7/9/2015
91092JohnDoeEnglish9073Single7/9/2015
101092JohnDoeVerbal2.8853Single7/9/2015
111092JohnDoeComprehensionMultiMultiMultiple7/9/2015
12
Sheet1


Is the text shown in range M9:M11, supposed to be in cell M3?
 
Upvote 0
lrgalvez2,

Here is a macro solution for you to consider based on your posted screenshots.

Sample raw data in two screenshots to fit the MrExcel display area:


Excel 2007
ABCDEFGHI
1IDFirstLastEmailDateLibraryTest(s)ScorePercentile
21091JuanCruzjuandelacruz@gmail.com2/2/2010Math2480
31092JohnDoejohn.doe@yahoo.com12/9/2011Battery 1English Comprehension Verbal90 2.88 Multi73 53 Multi
4
5
6
Sheet1



Excel 2007
JKLMN
1CompIndustry% RangeCompetencyAs of
2ABCFood71-100Multiple7/9/2015
3XYZCarFALSESingle Single Multiple7/9/2015
4
5
6
Sheet1
Cell Formulas
RangeFormula
L2=IF(I2<=30,"0-30",IF((AND(I2>30,I2<=70)),"31-70",IF((AND(I2>70,I2<=100)),"71-100")))
L3=IF(I3<=30,"0-30",IF((AND(I3>30,I3<=70)),"31-70",IF((AND(I3>70,I3<=100)),"71-100")))


After the macro in two screenshots to fit the MrExcel display area:


Excel 2007
ABCDEFGHIJ
1IDFirstLastEmailDateLibraryTest(s)ScorePercentileComp
21091JuanCruzjuandelacruz@gmail.com2/2/2010Math2480ABC
31092JohnDoejohn.doe@yahoo.com12/9/2011Battery 1English9073XYZ
41092JohnDoejohn.doe@yahoo.com12/9/2011Battery 1Comprehension2.8853XYZ
51092JohnDoejohn.doe@yahoo.com12/9/2011Battery 1VerbalMultiMultiXYZ
6
Sheet1



Excel 2007
KLMN
1Industry% RangeCompetencyAs of
2Food71-100Multiple7/9/2015
3Car71-100Single7/9/2015
4Car31-70Single7/9/2015
5CarFALSEMultiple7/9/2015
6
Sheet1
Cell Formulas
RangeFormula
L2=IF(I2<=30,"0-30",IF((AND(I2>30,I2<=70)),"31-70",IF((AND(I2>70,I2<=100)),"71-100")))
L3=IF(I3<=30,"0-30",IF((AND(I3>30,I3<=70)),"31-70",IF((AND(I3>70,I3<=100)),"71-100")))
L4=IF(I4<=30,"0-30",IF((AND(I4>30,I4<=70)),"31-70",IF((AND(I4>70,I4<=100)),"71-100")))
L5=IF(I5<=30,"0-30",IF((AND(I5>30,I5<=70)),"31-70",IF((AND(I5>70,I5<=100)),"71-100")))


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub ReorgSplitDataV2()
' hiker95, 07/09/2015, ME866439
Dim r As Long, lr As Long, s
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For r = lr To 2 Step -1
    If InStr(.Cells(r, 7), vbLf) Then
      s = Split(.Cells(r, 7), vbLf)
      .Rows(r + 1).Resize(UBound(s)).Insert
      .Cells(r + 1, 1).Resize(UBound(s), 6).Value = .Cells(r, 1).Resize(, 6).Value
      .Cells(r, 7).Resize(UBound(s) + 1) = Application.Transpose(s)
      s = Split(.Cells(r, 8), vbLf)
      .Cells(r, 8).Resize(UBound(s) + 1) = Application.Transpose(s)
      s = Split(.Cells(r, 9), vbLf)
      .Cells(r, 9).Resize(UBound(s) + 1) = Application.Transpose(s)
      .Cells(r + 1, 10).Resize(UBound(s), 2).Value = .Cells(r, 10).Resize(, 2).Value
      .Cells(r, 12).Copy .Cells(r + 1, 12).Resize(UBound(s))
      s = Split(.Cells(r, 13), vbLf)
      .Cells(r, 13).Resize(UBound(s) + 1) = Application.Transpose(s)
      .Cells(r + 1, 14).Resize(UBound(s)) = .Cells(r, 14)
    End If
  Next r
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgSplitDataV2 macro.
 
Upvote 0
Hiker your Code worked perfectly. No way could I have done this without assistance from you. I have over 10,000 rows to work with. Thank you so much.

Thank you to MrExcel for providing the venue for leeting this happen.

Regards to everyone
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
vba to make Alt+Enter into new row
Replies
6
Views
381
Alex Blakenburg
A
S
  • Question Question
Split lines in cell into rows while retaining font color in the new rows
Replies
4
Views
186
Subash_G
S
J
  • Solved
Enter multiple lines of text and auto-fit row height
Replies
6
Views
208
judgejustin
J
C
  • Question Question
Split into multiple files
Replies
3
Views
226
cute0angel0
C
N
  • Question Question
Construction example data - need to split data from columns into rows
Replies
2
Views
172
HighAndWilder
H

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top