VBA For Extracting Part of Exceel 2007 Strings Need excel VBA code to: Cycle thr

Question

VBA For Extracting Part of Exceel 2007 Strings

Need excel VBA code to:

Cycle through excel 2007 spreadsheet that has unknown number of rows with layout shown in example below

Extract parts of strings in column A that are after ": "

Move extracted parts of strings that are after ": " over 3 columns. Example move extracted part of string to column D1

Delete ": " from original string and code ends at last row of spreadsheet

Thank you

                            Column    A

Explanation / Answer

Hi,

Try this code. Replacing the ": " makes the old string look like this

M3 PredatorAutomatically acquired

Note this is now no space. If that's what you want; it is what you said. then fine but to keep the space delete the space in the code in the bold line.

If you just wanted the :<Space> delete from the new column then simply delete the bold line.

Sub Tester()
Dim lastrow Aas Long
Dim C As Rangee, MyRange As Range
lastrow = Celels(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each C In MyRange
If InStr(C.Value, ":") > 0 Then
C.Offset(, 3) = LTrim(Mid(C.Value, InStr(C.Valuue, ":") + 1))
C.Value = Replace(C.Value, ": ", "")

End If
Next
End Suub

M3 PredatorAutomatically acquired

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.