Forum Discussion
Solved
Delimiter for Separate Line Breaks in Cell not Working
I am trying to split a cell with three line breaks into 4 columns. When using the text to columns wizard and selecting delimited, all the text in the cell shows up in the preview. When I hit next, on...
HansVogelaar So I found a way to get it to work. All I did was edit the information that appears in the formula bar- like delete a word and then retype it-and somehow the text to column options works just fine.
Please let me know if you figure this out. 🙏
- I have not figured out how to get this to work using the text to columns wizard but I did find a macro that works:
Sub Delimit()
'splits Text active cell using ALT+10 char as separator
Dim splitVals As Variant
Dim totalVals As Long
Dim i As Integer
For i = 1 To 1000
splitVals = Split(ActiveCell.Value, Chr(10))
totalVals = UBound(splitVals)
Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row, ActiveCell.Column + 1 + totalVals)).Value = splitVals
ActiveCell.Offset(1, 0).Activate
Next i
End SubHere is a shorter macro. Select the cells with multiple lines (in a column) before running the macro.
Sub SplitLines() Selection.TextToColumns DataType:=xlDelimited, Other:=True, OtherChar:=vbLf End SubHansVogelaar So I found a way to get it to work. All I did was edit the information that appears in the formula bar- like delete a word and then retype it-and somehow the text to column options works just fine.
