VBA Split text string loop

[EMcK01]

Hi,

I've got a silly error here and can't think how to fix it. I have the following code that splits a text string for me based on the first space and offsets the string into the adjacent columns, that's all fine. However rather than looping through each cell in my range, it offsets the same string into all cells in the range then repeats this until it gets to the last cell in the range. So rather than having all the cells in my range split I essentially only have one cell split. As I said I'm sure its a silly error but can't think what I need to do to fix this.

Sub SplitText()

    Dim pos As Long
    Dim r As Range, c As Range


        Set r = Selection
        For Each c In r
          
            pos = InStr(c, " ")
                If pos Then
                r.Offset(, 1) = Trim(Left(c, pos - 1))
                r.Offset(, 2) = Trim(Mid(c, pos + 3))
            End If
    Next c
End Sub

I'm just wanting to keep the code as quick and simple that splits the range of cells that I manually highlight.

Thanks,
EMcK

 

[Rick Rothstein]

I think this macro will do what you want...

Sub SplitTextOnFirstSpaceOnly()
  Dim Cell As Range
  For Each Cell In Selection
    Cell.Offset(, 1).Resize(, 2) = Split(Cell.Value, " ", 2)
  Next
End Sub

 

[wideboydixon]

In your original code, you need to use c.Offset() instead of r.Offset()

I also notice that you move 3 places to the right of the space (thereby discarding three characters from the original string) and that cells with no space are ignored in which case Rick's solution isn't quite the same.

WBD

 

[Rick Rothstein]

I also notice that you move 3 places to the right of the space (thereby discarding three characters from the original string) and that cells with no space are ignored in which case Rick's solution isn't quite the same.

That's what I get for not actually reading the posted code. Thanks for pointing that out.

This should what the original code does (and without using a loop)...

[table="width: 500"]
[tr]
	[td]Sub SplitTextOnFirstSpaceOnly2()
  Selection.Offset(, 1) = Evaluate(Replace("IF(ISNUMBER(FIND("" "",@)),LEFT(@,FIND("" "",@&"" "")-1),"""")", "@", Selection.Address))
  Selection.Offset(, 2) = Evaluate(Replace("IF(@="""","""",MID(@,FIND("" "",@&"" "")+3,999))", "@", Selection.Address))
End Sub[/td]
[/tr]
[/table]

 

[EMcK01]

Gents,
Thanks for the quick response, easy and straightforward fix.
The reason for the offset was the text string was made up from a job number and job title separated by a hyphen, when splitting into separate cells there was no need to keep the hyphen so took the opportunity to delete it as part of this.
I had noticed Rick's first solution didn't quite work but I changed changing the code to Split(Cell.Value, " - ", 2) it gave the same result.

Thanks again.