CODEWITHSUNDEEP
excelvbastringtext
Renan Liporaci
Renan Liporaci

Reputation: 65

Loop on String, extract data between separators

So, I`m getting trouble trying to learn how to extract data from a single string with multiple data and separators.

I`m using commands "InStr" and "Left" to get the first part of data. My plan was to make a Loop, so VBA could get the first data, copy to another Cell, delete this data from the string, and do the same for each data group it can find on the string, but i cannot figure out how to do this. All my tryings i just get an error from the Loop.

I know i cannot use " - " to subtract a text from a string, but use "Replace" is the only option?

The data is separated by " ; " and i cannot know how many data groups the user will put on the table.

After loop and extract all data from the first cell string, i want to make another loop, so VBA can do the same for all cells with content, this part is not tricky at all, i can manage it, but let me ask for a advise, the better option for this is to make to separate Loops, i mean, a Loop inside another Loop?

This is the code i got working to just extract the frist data from the string.

Note: the string is like "aaaa;bbbb;ccccc;ddddd"

Sub TextSeparatorr()
Dim num_arq
Dim wks As Worksheet
Dim NewText
Set wks = Worksheets("Sheet1")
Dim Targeting
Dim Caminho_arq As String


num_arq = wks.Range("A:1").Value

wks.Range("A1").Select

Localiza = InStr(num_arq, ";")

wks.Range("B1").Value = Left(num_arq, Targeting - 1)

End Sub

Upvotes: 1

Views: 316

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

Typically Split() works best for this.

Eg:

Sub TextSeparator()

    Dim c As Range, arr

    For Each c In Worksheets("Sheet1").Range("A1:A20")
        If Len(c.Value) > 0 Then
            arr = Split(c.Value, ";")
            c.Offset(0, 1).Resize(1, UBound(arr) + 1).Value = arr
        End If
    Next c
    
End Sub

Upvotes: 3

Related Questions