Extracting value from multiple lines in cell

[Ro93rt]

I have values in a column of cells in the format (All numbers in a single cell)

34567,43510,'1'
44049,99820,'1'
11124,37373,'1'
etc

up to ten such entries may appear in each cell with an arbitrary number in each cell. They were entered using the ALT+Enter method. Is there an easy formula to extract each row (such as 44049,99820,'1') to use in a formula. If there are two entries I would like to write two separate rows like:

LINE 34567,43510,'1'
LINE 44049,99820,'1'

If a macro would be required I understand and could use that also.

I did a quick look and did not see a similar post but I am willing to go back and do a more thorough search if someone recalls that this was dealt with previously.

Ro93rt.

 

[hiker95]

Ro93rt,

Welcome to the MrExcel forum.


Sample raw data beginning in cell A1:

Excel Workbook
	A
1	34567,43510,'1'44049,99820,'1'11124,37373,'1'
2	34567,43510,'2'44049,99820,'2'11124,37373,'2'
3
4
5
6
7
Sheet1

After the macro:

Excel Workbook
	A
1	34567,43510,'1'
2	44049,99820,'1'
3	11124,37373,'1'
4	34567,43510,'2'
5	44049,99820,'2'
6	11124,37373,'2'
7
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.

Option Explicit
Sub ReorgData()
' hiker95, 06/07/2012
' http://www.mrexcel.com/forum/showthread.php?t=640575
Dim r As Long, lr As Long, s 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

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