jeudi 26 février 2015

Excel Macro: If the characters " XX " are in the middle of a string then split the string at that point

I'm trying to divide a column into two columns based on finding a specific characters in the string.


Specifically, if ColumnA contains the string " XX " then, copy all before " XX " in ColumnD, and copy " XX " & everything after to ColumnF.



Sub splitit()
Dim i As Long, l As Long
l = Cells(Rows.Count, 1).End(xlUp).Row
For i = l To 1 Step -1
If InStr(Cells(i, 1).Value, celltxt, " XX ") Then
Cells(i, 4).Value = Left(Cells(i, 1).Value, Find(" XX ", Cells(i, 1).Value) - 1)
Cells(i, 6).Value = " XX " & Mid(Cells(i, 1).Value, Find(" XX ", Cells(i, 1).Value) + 1, Len(Cells(i, 1).Value))
End if
Next i
End Sub

Aucun commentaire:

Enregistrer un commentaire