CODEWITHSUNDEEP
excelvbafor-loopinstr
MaNaR
MaNaR

Reputation: 47

vba program to extract word between two special caracters from cells value

I have an .xlsm doc that contains hundreds of rows, and I want to set the value of C column based on B column.

For example : B1 contains " + New node '145872-12547-4885' created "

My purpose is to extract using a macro that loop all rows just the text between the two " ' " .. in this case -- 145872-12547-4885 --

Thanks in advance,

Sub extract()

    Dim N As Long, i As Long, j As Long


    N = Cells(Rows.Count, "B").End(xlUp).Row

    j = 1

    For i = 1 To N

           If Left(Cells(i, "B"), 11) = " + New node" Then

           Cells(j, "C").Value = Mid(Cells(i, "B"), InStr(1, Cells(i, "B").Value, "'") + 1, Len(Cells(i, "B")) - InStr(1, Cells(i, "B").Value, "'") - 1)

            j = j + 1

          End If

    Next i

End Sub

Thanks but I still have some troubles,

I want that the extract will be in front of cell that contain the origin value, and it's not the case :

enter image description here because i need too to extract the code in the begining of cells that end by "SEL_AFFILIATE" and i don't know how to do it using SPLIT This is my code :

Sub extract()

Dim N As Long, i As Long, j As Long, s As String

N = Cells(Rows.Count, "B").End(xlUp).Row

j = 1

For i = 1 To N

        s = Cells(i, "B").Text

            If Left(s, 11) = " + New node" Then

                Cells(j, "C").Value = Split("'" & s, "'")(2)

                j = j + 1

            End If

            'If Right(s, 14) = "SEL_AFFILIATE " Then

               ' Cells(j, "C").Value = Split("" & s, ".")(2)

               ' j = j + 1

            'End If          
Next i
End Sub

Upvotes: 0

Views: 58

Answers (3)

MaNaR
MaNaR

Reputation: 47

Now I want to extract the ending of my cell content if the begining = "In File"

This is my cell

I tried this code but in vain :

        If Left(s, 8) = " In File" Then

            Cells(i, "B").Offset(0, 1).Value = Split(",", s)(0)

        End If

Upvotes: 0

Mikku
Mikku

Reputation: 6654

Try:

Sub extract()

Dim N As Long, i As Long, j As Long, s As String

N = Cells(Rows.Count, "B").End(xlUp).row


For i = 1 To N

        s = Cells(i, "B").Text

            If Left(s, 11) = " + New node" Then

                Cells(i, "B").Offset(0, 1).Value = Split("'" & s, "'")(2)


            End If

            If Right(s, 14) = "SEL_AFFILIATE " Then

                Cells(i, "B").Offset(0, 1).Value = Split(s, ".")(0)

            End If

Next i

End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

You could use Split():

Sub extract()

    Dim N As Long, i As Long, j As Long, s As String


    N = Cells(Rows.Count, "B").End(xlUp).Row

    j = 1

    For i = 1 To N
            s = Cells(i, "B").Text
           If Left(s, 11) = " + New node" Then
                Cells(j, "C").Value = Split("'" & s, "'")(2)
            j = j + 1
          End If
    Next i

End Sub

enter image description here

EDIT#1:

For a description of the Split() function, see:

Reference

Upvotes: 1

Related Questions