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
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