vba split text string

[EMcK01]

Hi,

looking for some help with vba to split a text string. The code I have is as follows:

Sub SplitText()

    Dim numRows As Long
    Dim arrText As Variant
    Dim rw As Long
    With Sheets("Sheet1")
      'get the number of rows
      numRows = .Cells(.Rows.Count, "B").End(xlUp).Row
      
      For rw = 3 To numRows
         
         'test for ":" is in string, if so, split into array and process
         If InStr(.Range("B" & rw).Value, ":") Then
            arrText = split(.Range("B" & rw).Value, ":")   'split string on :
            
            'output          
            .Range("F" & rw).Value = arrText(0)
            .Range("H" & rw).Value = arrText(1)


         End If
      Next rw
   End With


End Sub

The code works fine but I've updated from when it was first written for me and here is my issue. I want to use it to split a string based a 'time' within the string ie cell B3 contains "abc de12:30 fg hij" and I would like to split it so cell F3 contains "abc de", "fg hij" in cell G3 and 'time' part of the string "12:30" in cell H3

The pattern of the text in the string can vary in number of words of characters so needs to be split based on the 'time'

Thanks,
EMcK

 

[JLGWhiz]

Change this

.Range("F" & rw).Value = arrText(0)
.Range("H" & rw).Value = arrText(1)

To this

.Range("F" & rw) = Left(arrText(0), Len(arrText(0)) - 2) 
.Range("G" & rw) = Mid(arrText(1), 3)
.Range("H" & rw) = Right(arrText(0), 2) & ":" & Left(arrText(1), 2)

 

[EMcK01]

Perfect, thanks for your help, very much appreciated

 

[Peter_SSs]

For what it's worth, this could be done without the Split function, and probably a bit simpler overall

With a couple of extra variables (& you wouldn't need arrText)

Dim pos as long
Dim s as string

Then alter this bit of your code.

For rw = 3 To numRows
   s = .Range("B" & rw).Value
   pos = InStr(s, ":")
   If pos Then
      .Range("F" & rw) = Left(s, pos - 3)
      .Range("G" & rw) = Mid(s, pos + 3)
      .Range("H" & rw) = Mid(s, pos - 2, 5)

Added note: You may also want to add a TRIM() around some/all of the results unless you actually want any leading/trailing space(s) that this and the previous code produces.

 

[JLGWhiz]

Perfect, thanks for your help, very much appreciated

You're welcome,
regards, JLG

 

[EMcK01]

Hi Pete, thanks for your input. As I think you know I'm still very much a beginner when it comes to vba.

I had thought about the TRIM() function, I had used the following at another point in the code:

    Set ws = ThisWorkbook.Sheets("Sheet1")     
    With ws
    
    'i = ws.Rows.Count
    i = .Cells(.Rows.Count, "B").End(xlUp).Row
   
        Set rng = .Range("F3", .Range("H" & i).End(xlUp))
    End With
    
    With rngA = rng.Select
    Call trimtext
    End With

Sub trimtext()    Dim r As Range, c As Range
    Set r = Selection
    For Each c In r
        c = Trim(c)
    Next c
End Sub

It works for what I want it to do but I suspect is more complicated than it needs to be. Is there an easy way to integrate it with the code you provided?

Also, out of curiosity/further learning, why would you not use the Split function?

Thanks,
EMcK

 

[Peter_SSs]

Is there an easy way to integrate it with the code you provided?

I would incorporate it directly into the code like this

If pos Then
      .Range("F" & rw) = Trim(Left(s, pos - 3))
      .Range("G" & rw) = Trim(Mid(s, pos + 3))
      .Range("H" & rw) = Trim(Mid(s, pos - 2, 5))

Also, out of curiosity/further learning, why would you not use the Split function?

Only relevant if the data is very large, but the Instr method would use less resources & be faster than the Instr (you were already using Instr in your code) and Split method.

 

[EMcK01]

Thanks for the update, much appreciated. Also somewhat simpler coding than the way I was doing it...