Change the # of data lines per cell

canadian86

Board Regular
Joined
Feb 6, 2011
Messages
53
Hi all,

I have a cell with 4 lines of data WITHIN the cell. (I pressed ALT + Enter as I was inserting a new line within the cell.)

I now want to put the 4 lines within that cell into 4 different lines.

Is there a quick way to do this? I have 150 cells with multiple lines of data and I want to end up having 1 line per cell.

Here's an example of what I mean:

https://docs.google.com/open?id=0B8GXPbMcTrZIZGFjYjg4ZTUtYTUxNC00NDc0LTkyZTAtNzU5Nzc2MmM4M2M2

Thanks!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
canadian86,


Sample raw data before the macro:


Excel Workbook
A
1Strategic PlanCompetitive Activity (External)Ecnomic Conditions (External)Exploration AnalysisResource / Reserve Statement
2Strategic Plan #2Competitive Activity (External) #2Ecnomic Conditions (External) #2Exploration Analysis #2Resource / Reserve Statement #2
3Strategic Plan #3Competitive Activity (External) #3Ecnomic Conditions (External) #3Exploration Analysis #3Resource / Reserve Statement #3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sheet1





After the macro:


Excel Workbook
A
1Strategic Plan
2Competitive Activity (External)
3Ecnomic Conditions (External)
4Exploration Analysis
5Resource / Reserve Statement
6Strategic Plan #2
7Competitive Activity (External) #2
8Ecnomic Conditions (External) #2
9Exploration Analysis #2
10Resource / Reserve Statement #2
11Strategic Plan #3
12Competitive Activity (External) #3
13Ecnomic Conditions (External) #3
14Exploration Analysis #3
15Resource / Reserve Statement #3
16
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, by highlighting the code and pressing the keys CTRL + C
2. Open your 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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code:
Option Explicit
Sub SplitData()
' hiker95, 11/08/2011
' http://www.mrexcel.com/forum/showthread.php?t=590797
Dim lr As Long, r As Long, Sp
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 1 Step -1
  Sp = Split(Cells(r, 1), Chr(10))
  Rows(r + 1).Resize(UBound(Sp)).Insert
  Cells(r, 1).Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)
Next r
Application.ScreenUpdating = True
End Sub


Then run the SplitData macro.
 
Upvote 0
Thanks a lot! It seems to work when run the macro after highlighting 3 cells. But when I highlight all 150 cells and run it, it gives me this error:


-- removed inline image ---
Runtime error 1004

When i click debug, it says highlights this line in yellow:

Rows(r + 1).Resize(UBound(Sp)).Insert

Any ideas how to fix this?
 
Upvote 0
So it split the first 50 cells in the first 50 rows. On the 51st row, I have this text in the cell "Existing policies" -- its only 1 row, so it doesn't need to be split further. Is this why the code didn't work?
 
Upvote 0
canadian86,

You want to highlite a group of contiguous cells in column A, and have the macro work just on those selected cells?
 
Last edited:
Upvote 0
canadian86,


Sample raw data, with just a selection of contiguous cells, say range A1:A2, that is selected before the macro:


Excel Workbook
A
1Strategic PlanCompetitive Activity (External)Ecnomic Conditions (External)Exploration AnalysisResource / Reserve Statement
2Strategic Plan #2Competitive Activity (External) #2Ecnomic Conditions (External) #2Exploration Analysis #2Resource / Reserve Statement #2
3Strategic Plan #3Competitive Activity (External) #3Ecnomic Conditions (External) #3Exploration Analysis #3Resource / Reserve Statement #3
4Existing policies
5
Sheet1





After the updated macro:


Excel Workbook
A
1Strategic Plan
2Competitive Activity (External)
3Ecnomic Conditions (External)
4Exploration Analysis
5Resource / Reserve Statement
6Strategic Plan #2
7Competitive Activity (External) #2
8Ecnomic Conditions (External) #2
9Exploration Analysis #2
10Resource / Reserve Statement #2
11Strategic Plan #3Competitive Activity (External) #3Ecnomic Conditions (External) #3Exploration Analysis #3Resource / Reserve Statement #3
12Existing policies
13
Sheet1





If you only select one cell, say A1, before the macro:


Excel Workbook
A
1Strategic PlanCompetitive Activity (External)Ecnomic Conditions (External)Exploration AnalysisResource / Reserve Statement
2Strategic Plan #2Competitive Activity (External) #2Ecnomic Conditions (External) #2Exploration Analysis #2Resource / Reserve Statement #2
3Strategic Plan #3Competitive Activity (External) #3Ecnomic Conditions (External) #3Exploration Analysis #3Resource / Reserve Statement #3
4Existing policies
5
Sheet1





After the macro:


Excel Workbook
A
1Strategic Plan
2Competitive Activity (External)
3Ecnomic Conditions (External)
4Exploration Analysis
5Resource / Reserve Statement
6Strategic Plan #2Competitive Activity (External) #2Ecnomic Conditions (External) #2Exploration Analysis #2Resource / Reserve Statement #2
7Strategic Plan #3Competitive Activity (External) #3Ecnomic Conditions (External) #3Exploration Analysis #3Resource / Reserve Statement #3
8Existing policies
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).


Code:
Option Explicit
Sub SplitSelection()
' hiker95, 11/08/2011
' http://www.mrexcel.com/forum/showthread.php?t=590797
Dim lr As Long, sr As Long, sc As Long, er As Long, r As Long, Sp
Application.ScreenUpdating = False
sr = ActiveCell.Row
sc = Selection.Column
er = Selection.Rows.Count
lr = sr + er - 1
For r = lr To sr Step -1
  Sp = Split(Cells(r, sc), Chr(10))
  Rows(r + 1).Resize(UBound(Sp)).Insert
  Cells(r, sc).Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)
Next r
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


Then run the SplitSelection macro.
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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