@SarahD33
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.
