Split cell contents based on carriage returns?

O

olly_w

Board Regular
Joined
Jul 2, 2003
Messages
189
Hi

I have a dataset which contains cell entries with multiple pieces of data, separated by a carriage return (i.e. ALT+ENTER).

Is there a simple formula i can use to extract the cell contents based on the carraige return? For example, if i had Cell A which contained the following

123 OJW
222 LLS
333 MR ET

Could i split this one cell out into 3 different cell entries?

Any advice greatly appreciated

Ol
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
This macro should do it:

Code: 
Sub removeCR()
Activecell.TextToColumns Destination:=Activecell, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=Chr(10), FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub
I don't know the shortcut keys for a carriage return otherwise you could do this without a macro within text to columns I believe. You can also expand this to a range and not just the active cell.

Hope that helps.
 
Upvote 0
Hi, thanks for this, but what I would ideally like to do is to split the cell contents out within the same column, but following row.

For instance, if I have a cell with 3 values in separated by carriage returns, I would like that to be spread across 3 cells going downwards, shifting the whole workbook down.

If it can do that it would be great...

thanks
 
Upvote 0
also, if it could work based on a whole column's contents that would be awesome....

thanks in advance!
 
Upvote 0
So your example above you want to split into rows 1,2 and 3? Will there be data in rows 1, 2 and 3 already?
 
Upvote 0
Hi

Basically, I have a workbook with entries separated with carriage returns from cell H5 to cell H703. What I need to do is for each cell (i.e. H5 for instance), I need to split the cell out into the cells below (i.e. if there are 3 entries in cell H5, then I would need the macro to split the contents so that the first value is left retained in H5, and then value 2 in H6 and value 3 in H7.

The only caveat is that there will be values in the cells underneath H5, so when the macro runs and determines how many values their are in cell H5, then it will first need to insert that many rows (-1) below H5, and then split out the contents.

Hope that makes sense; if you can get anything like that to work it would be great!

Regards

Ol
 
Upvote 0
Could you post some more sample data?

Is it always going to be 3 pieces of data?

Are you 100% sure it's a carriage return seperating them?
 
Upvote 0
Hi Ol

ALT-ENTER does not insert a carriage return, it inserts a line feed.

Try for column H, with the data starting at row 5, down (or adapt the range):


Code: 
Sub SplitCells()
Dim rColumn As Range
Dim lFirstRow As Long
Dim lLastRow As Long
Dim lRow As Long
Dim lLFs As Long
 
Set rColumn = Columns("H")
lFirstRow = 5
lLastRow = rColumn.Cells(Rows.Count).End(xlUp).Row
 
For lRow = lLastRow To lFirstRow Step -1
    lLFs = Len(rColumn.Cells(lRow)) - Len(Replace(rColumn.Cells(lRow), vbLf, ""))
    If lLFs > 0 Then
        rColumn.Cells(lRow + 1).Resize(lLFs).Insert shift:=xlShiftDown
        rColumn.Cells(lRow).Resize(lLFs + 1).Value = Application.Transpose(Split(rColumn.Cells(lRow), vbLf))
    End If
Next lRow
End Sub
 
Upvote 0
Try the following code - it assumes data is in range A1:A21 of the data sheet - replace this with your actual range (single column).
The macro should be run with the data sheet active.
The parsed list will be pasted over the original data

Code: 
Sub ParseData()
    Dim SourceRng As Range
    Dim Wk As Worksheet
    Set SourceRng = Range("A1:A21")
    Set OldSht = SourceRng.Parent
    Addr = SourceRng.Range("A1").Address
    Set Wk = Worksheets.Add
    SourceRng.Copy Wk.Range("A1")
    Set SourceRng1 = Wk.Range("A1").Resize(SourceRng.Rows.Count, 1)
    SourceRng.ClearContents
    SourceRng1.TextToColumns Destination:=Wk.Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=Chr(10), FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    For i = 1 To SourceRng1.Rows.Count
        Set CopyRng = SourceRng1.Cells(i, 1).Resize(1, WorksheetFunction.CountA(SourceRng1.Cells(i, 1).EntireRow))
        CopyRng.Copy
        Set PasteRng = IIf(i = 1, OldSht.Range(Addr), OldSht.Range("A65536").End(xlUp)(2))
        PasteRng.PasteSpecial Transpose:=True
    Next
    Application.DisplayAlerts = False
    Wk.Delete
    Application.DisplayAlerts = True
End Sub
 
Upvote 0
As one final question to this thread. I have a similar issue,
Column A has a unique client identifier for multiple notes in column B that are in the same cell, but with a carriage return between them.

The code above splits them TTC style, but how do I get them to go down the page and copy the unique identifier from column A along with it, so it goes from looking like

Column A Column B
A10 -------Note A1 (carrige return) Note A2 (carrige return) Note A3
B10 -------Note B1 (carrige return) Note B2
C10 -------Note C1

Column A Column B
A10 -------Note A1
A10 -------Note A2
A10 -------Note A3
B10 -------Note B1
B10 -------Note B2
C10 -------Note C1

(dashes used for layout purposes, forum post drops multiple spaces)

Thanks in advance,

Ben H.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Trouble splitting text to columns
Replies
6
Views
422
Peter_SSs
Peter_SSs
M
  • Question
Copy just contents of a cell without a carriage return?
Replies
5
Views
818
Jaafar Tribak
Jaafar Tribak
D
  • Question
Split cell with Carriage returns, then loop code for only specific sheets
Replies
8
Views
892
jdellasala
J
M
  • Question
Returning Values Based on Referent Cells (Avoiding Redundant Values)
Replies
5
Views
268
Fluff
Fluff
G
  • Question
Macro to consolidate multiple worksheets into Master based on cell data?
Replies
1
Views
293
bobaol
B

Forum statistics

Threads
1,215,327
Messages
6,124,290
Members
449,149
Latest member
mwdbActuary

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