VBA split text in columns but skip rows when there is data without spacebar used

[Pätkis]

Hey,

I have a problem with my macro below. It splits text in columns when there is space between text but it stops if there is a cell with one word / number and no space used. How can I get it skip rows, which it cannot split in columns?

Sub SplitText()
Dim StringArray() As String, Cell As Range, i As Integer
For Each Cell In Selection.Range("A1:A150")
StringArray = Split(Cell, " ")
For i = 0 To UBound(StringArray)
Cell.Offset(, i + 1) = StringArray(i)
Next i
Next
End Sub

Here is an example what it does but stops doing when there isn't space

 

[Pätkis]

This I can do easily using the code below.
If you want to split out characters that do not have the space delimiter then you are going to have to hard code quite a bit more stuff into your macro.
(I suspect difference would normally have been entered as =difference and will be caught up the single value if statement and produce nothing, here I had it entered as = difference [with a space after the equals])

20210728 VBA Split skip single value.xlsm
	A	B	C	D	E
1	Hello world	Hello	world
2	Try one more	Try	one	more
3	SingleValue
4	try again	try	again
5	Formula =Test	Formula	=Test
6	#NAME?	=-closing	month
7	#NAME?	=	difference
8	test range	test	range
9
Data

Cell Formulas
Range	Formula
A5	A5	= "Formula " & "=Test"
A6	A6	=-closing month
A7	A7	= difference
A8	A8	=RangeName

Named Ranges
Name	Refers To	Cells
RangeName	=Data!$K$1	A8

Sub SplitText()
    Dim StringArray() As String, Cell As Range, i As Integer
    Dim strCell As String
 
    For Each Cell In Range("A1:A150")
 
    If IsError(Cell) Then
        strCell = Cell.Formula
    Else
        strCell = Cell.Value
    End If
 
    StringArray = Split(Trim(strCell), " ")
        If UBound(StringArray) <> 0 Then
            For i = 0 To UBound(StringArray)
                If Left(StringArray(i), 1) = "=" Then
                    Cell.Offset(, i + 1) = Replace(StringArray(i), "=", "'=")
                Else
                    Cell.Offset(, i + 1) = StringArray(i)
                End If
            Next
        End If
    Next
End Sub

Thank you! This solved my problem

 

[Alex Blakenburg]

Glad we could help and thanks for the feedback.