Mar 22 2019 11:03 AM
We are migrating from one database to another. I have a customer spreadsheet with an address block which is all in 1 column but separated by line breaks that needs splitting into 6 columns. When you click the cell it looks like this:
National Centre for Learning Welsh
Aberystwyth University
P5 Campws Penglais
Aberystwyth
Ceredigion
SY23 3UX
When I press 'text to columns' it only transfers the 1st line and ignores the rest - even in the preview.
I am using Ctrl J as the delimiter.
I have done find and replace to swap the line break for different delimiter characters (e.g. ~ ; ^) e.g.
National Centre for Learning Welsh~Aberystwyth University~P5 Campws Penglais~Aberystwyth~Ceredigion ~SY23 3U
but it still ignores everything after the first line. I've tried it with a test (test~test~test) just typed in to make sure I'm doing it correctly and that works.
Mar 22 2019 05:48 PM
Mar 23 2019 04:11 AM
@Axle-Flipps_1 , looks strange. Could you please provide small sample Excel file to check? (info as in your post will be enough).
Apr 12 2022 08:13 AM
Oct 19 2022 08:32 AM
@Axle-Flipps_1 I am having the exact same problem! Did you find a solution? Even when I replaced all of the carriage returns with a comma for instance, it still isn't working. I'll select a bunch of rows, but even the preview will only show the separated results for the first row/cell. If I start the process over again one row down, it will then give me the separated results for that row. I cannot for the life of me figure this out!
Oct 19 2022 09:14 AM
Oct 19 2022 10:22 AM
Hello Sarah, could you provide some of your data to be able to perform an analysis here. Without the original data that does not work, it can not work.
If you can't share documents here, then please use another file sharing service and post the link here.
BTW: What is CTRL+ J for a shortcut. Where does it do something? Is it a character or formatting in a program? Is it from the Mac? It doesn't seem to do anything in Excel on Windows.
Oct 19 2022 11:19 AM - edited Mar 22 2023 12:50 AM
As this seems to be a problem many people (including myself) have encountered...
How about executing a VBA procedure (shown below) to split the text? You would not have to save the VBA procedure in the workbook; you could either create it in your PERSONAL.xlsb file, or just store it in the workbook temporarily, and delete the code after use.
Sub SplitTextToColumns()
' This procedure will split the text in a column of the currently-
' selected row into separate adjacent columns in the same row.
' The split occurs where a particular delimiter is found, which
' is specified by the user.
' The procedure splits the text in all rows from the current row down
' until it reaches an empty cell (or a cell with an empty string
' value) in the source-text column. Code is included to warn the
' user if some non-empty string value is about to be overwritten.
Const strPROCEDURE_NAME As String = "SplitTextToColumns"
Dim strWorksheetName As String
'
Dim strMessage As String
Dim in4UserResponse As VbMsgBoxResult
'
Dim strDelimiter As String
Dim strSourceCol As String
'
Dim in4CurrentRow As Long
Dim objRange As Range
Dim strCellContent As String
Dim strPieces() As String
Dim in4PieceIndex As Long
Dim in4RelativeCol As Long
'---- Determine the active worksheet and row.
strWorksheetName = ActiveSheet.Name
in4CurrentRow = ActiveCell.Row
'---- Get confirmation and option choices from the user.
strMessage = "Are you sure you want to split cell contents" _
& " in worksheet " & strWorksheetName _
& ", starting at row " & CStr(in4CurrentRow) _
& " and overwriting adjacent columns to the right?"
in4UserResponse = MsgBox(strMessage, vbQuestion Or vbYesNo _
Or vbDefaultButton2, strPROCEDURE_NAME)
If in4UserResponse = vbNo Then
Exit Sub
End If
' -- Prompt for the column.
strMessage = "Which column contains the text to be split? (Enter" _
& " the letter(s).)"
strSourceCol = InputBox(strMessage, strPROCEDURE_NAME, "A")
If strSourceCol = "" Then
strMessage = "That's required information. Quitting."
Call MsgBox(strMessage, vbInformation Or vbOKOnly, strPROCEDURE_NAME)
Exit Sub
ElseIf Len(strSourceCol) > 3 Then
strMessage = "Invalid input. Quitting."
Call MsgBox(strMessage, vbInformation Or vbOKOnly, strPROCEDURE_NAME)
Exit Sub
ElseIf Len(strSourceCol) = 3 _
And UCase$(strSourceCol) >= "XED" Then
strMessage = "Too far to the right. Quitting."
'...that far out, the text is likely to be split beyond the
' available columns.
Call MsgBox(strMessage, vbInformation Or vbOKOnly, strPROCEDURE_NAME)
Exit Sub
Else
strSourceCol = UCase$(strSourceCol)
End If
' -- Prompt for the delimiter.
strMessage = "Enter the delimiter character, or TAB or LF or CR" _
& " for a special character:"
strDelimiter = InputBox(strMessage, strPROCEDURE_NAME)
If strDelimiter = "" Then
strMessage = "That's required information. Quitting."
Call MsgBox(strMessage, vbInformation Or vbOKOnly, strPROCEDURE_NAME)
Exit Sub
ElseIf UCase$(strDelimiter) = "TAB" Then
strDelimiter = Chr(9) 'tab character
ElseIf UCase$(strDelimiter) = "LF" Then
strDelimiter = Chr(10) 'line feed character
ElseIf UCase$(strDelimiter) = "CR" Then
strDelimiter = Chr(13) 'carriage return character
ElseIf Len(strDelimiter) > 1 Then
strMessage = "Invalid input. Quitting."
Call MsgBox(strMessage, vbInformation Or vbOKOnly, strPROCEDURE_NAME)
Exit Sub
Else
'Use the value in strDelimiter as-is.
End If
'---- Process the current row and following rows.
With Worksheets(strWorksheetName)
Do
Set objRange = .Range(strSourceCol & CStr(in4CurrentRow))
objRange.Select
'
strCellContent = Selection.Value
If strCellContent = "" Then
'...we're done.
strMessage = "Stopping at 'empty' cell in row " _
& CStr(in4CurrentRow)
Call MsgBox(strMessage, vbInformation Or vbOKOnly)
Exit Do
End If
' -- Do the work for this row.
strPieces = Split(strCellContent, strDelimiter)
For in4PieceIndex = 0 To UBound(strPieces)
in4RelativeCol = in4PieceIndex + 1
strCellContent = objRange.Offset(0, in4RelativeCol).Value
If strCellContent <> "" Then
strMessage = "WARNING! Row " & CStr(in4CurrentRow) _
& " is not empty in a destination cell. Is It" _
& " OK to overwrite the contents of this row?"
in4UserResponse = MsgBox(strMessage _
, vbExclamation Or vbYesNoCancel Or vbDefaultButton2 _
, strPROCEDURE_NAME)
If in4UserResponse = vbNo Then
GoTo NextRow
ElseIf in4UserResponse = vbCancel Then
Exit Do
Else
'...continue with the user's permission.
End If
End If
objRange.Offset(0, in4RelativeCol).Value = _
strPieces(in4PieceIndex)
Next in4PieceIndex
NextRow:
' -- Advance a row.
in4CurrentRow = in4CurrentRow + 1
Application.Goto Reference:="R" & CStr(in4CurrentRow) _
& "C1"
DoEvents
Loop
End With
End Sub
If you find that your invisible delimiter is not a line feed character (LF) or a carriage return character (CR), you can add code following the comment "Prompt for the delimiter" to handle a different delimiter.
2023-03 addendum: Before/during September 2022 (shortly before I posted this solution), Microsoft added the built-in function TEXTSPLIT to the latest versions of Excel. TEXTSPLIT also supports the use of multiple delimiters in one pass. So while the VBA code above is still usable (and of course, can be customized for special circumstances), the use of TEXTSPLIT might be a better, quicker choice.
Oct 20 2022 12:41 PM
Jul 21 2023 12:19 AM
@SnowMan55 oh wow!!!! Thank you so much. I tried using the TEXTSPLIT and couldn't get it to work, but your VBA procedure worked a treat! I had never used Visual Basic in a spreadsheet before, but it was pretty easy to do with your code. Thank you, thank you, thank you. There were fist pumps in the air when it worked!!
Jul 21 2023 05:51 AM
Why don't combination of TEXTJOIN/TEXTSPLIT doesn't work?
Jul 21 2023 09:31 AM
@Axle-Flipps_1 Could be solved with a combination of Sequence and index?
INDEX(B3:B20;SEQUENCE(COUNTA(B3:B20)/6;6))
See attached file for example.
/Geir