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

Pätkis

New Member
Joined
Jan 30, 2019
Messages
11
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
1627452769033.png
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,058
Office Version
  1. 365
Platform
  1. Windows
Do you really intend what this line is doing though
Selection.Range("A1:A150")
It starts the range at the first cell in whatever cell or range you select and runs the macro for the next 150 rows.
So if you select E8 it runs from E8:E157

I haven't adjusted for the above comment but try this
VBA Code:
Sub SplitText()
    Dim StringArray() As String, Cell As Range, i As Integer
    For Each Cell In Selection.Range("A1:A150")
    StringArray = Split(Trim(Cell), " ")
        If UBound(StringArray) <> 0 Then
            For i = 0 To UBound(StringArray)
                Cell.Offset(, i + 1) = StringArray(i)
            Next
        End If
    Next
End Sub
 

Pätkis

New Member
Joined
Jan 30, 2019
Messages
11
I changed the selection.range to be range. Thanks for that!

But the code gives me run-time error '13': Type mismatch
1627456717485.png


There is two lines in excel, which has = sign first and excel shows them as #NAME?. If I remove these two lines macro works like a dream but is there a possibility two run the macro without deleting these two lines?
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,058
Office Version
  1. 365
Platform
  1. Windows
See if this does what you need.
I am prefixing it with a single quote so it doesn't think it is a formula.

VBA Code:
Sub SplitText()
    Dim StringArray() As String, Cell As Range, i As Integer
    For Each Cell In Range("A1:A150")
    StringArray = Split(Trim(Cell), " ")
        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
 

Pätkis

New Member
Joined
Jan 30, 2019
Messages
11

ADVERTISEMENT

Unfortunately it is still giving the same error.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,058
Office Version
  1. 365
Platform
  1. Windows
Can you paste into here what is in the cell to start with and also what you want as output.

This was my sample data

20210728 VBA Split skip single value.xlsm
ABCDE
1Hello worldHelloworld
2Try one moreTryonemore
3SingleValue
4try againtryagain
5Formula =TestFormula=Test
6
Data
Cell Formulas
RangeFormula
A5A5= "Formula " & "=Test"
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

I have modified Alex's code below - does it work correctly for you?
VBA Code:
Sub SplitText()
  Dim StringArray() As String, Cell As Range, Rng As Range, i As Integer
  Set Rng = Selection(1).Resize(150)
  For Each Cell In Union(Rng.SpecialCells(xlConstants), Rng.SpecialCells(xlFormulas, xlTextValues))
    StringArray = Split(Trim(Cell), " ")
    If UBound(StringArray) <> 0 Then
      For i = 0 To UBound(StringArray)
        Cell.Offset(, i + 1).NumberFormat = "@"
        Cell.Offset(, i + 1) = StringArray(i)
      Next
    End If
  Next
End Sub
 

Pätkis

New Member
Joined
Jan 30, 2019
Messages
11
Can you paste into here what is in the cell to start with and also what you want as output.

This was my sample data

20210728 VBA Split skip single value.xlsm
ABCDE
1Hello worldHelloworld
2Try one moreTryonemore
3SingleValue
4try againtryagain
5Formula =TestFormula=Test
6
Data
Cell Formulas
RangeFormula
A5A5= "Formula " & "=Test"
Hi

This is what I have and the what I would want the macro to be.
Closing YearClosingYear
#NAME?​
=-ClosingMonth
#NAME?​
=Difference
Gross Tax NetGrossTaxNet
1568,45 1983,21 19821,24 1 25,00 %
1568,45​
1983,21​
19821,24​
1​
25,00​
%
 

Pätkis

New Member
Joined
Jan 30, 2019
Messages
11
I have modified Alex's code below - does it work correctly for you?
VBA Code:
Sub SplitText()
  Dim StringArray() As String, Cell As Range, Rng As Range, i As Integer
  Set Rng = Selection(1).Resize(150)
  For Each Cell In Union(Rng.SpecialCells(xlConstants), Rng.SpecialCells(xlFormulas, xlTextValues))
    StringArray = Split(Trim(Cell), " ")
    If UBound(StringArray) <> 0 Then
      For i = 0 To UBound(StringArray)
        Cell.Offset(, i + 1).NumberFormat = "@"
        Cell.Offset(, i + 1) = StringArray(i)
      Next
    End If
  Next
End Sub
This gives run-time error ´1004´: No cells were found.
1627463123334.png
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,058
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1Hello worldHelloworld
2Try one moreTryonemore
3SingleValue
4try againtryagain
5Formula =TestFormula=Test
6#NAME?=-closingmonth
7#NAME?=difference
8test rangetestrange
9
Data
Cell Formulas
RangeFormula
A5A5= "Formula " & "=Test"
A6A6=-closing month
A7A7= difference
A8A8=RangeName
Named Ranges
NameRefers ToCells
RangeName=Data!$K$1A8



VBA Code:
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
 
Solution

Forum statistics

Threads
1,144,341
Messages
5,723,806
Members
422,518
Latest member
quack_quack

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
Top