Embedded Files
Excel Macro for cleaning Keyword Shitter data.
The data placed at the bottom of Keyword Shitter from the Keywords Everywhere app can be cleaned by using the following macro.
Simply copy and paste the data list from Keyword Shitter and paste into an Excel sheet in A1. Paste it in as plain text.
The run the following macro.
Sub Cleaning_Keyword_Shitter_Data()
'
' Cleaning_Keyword_Shitter_Data Macro
' Cleans the data from Keyword Shitter and places the data into separate columns with filter headers.
'
'
Cells.Select
Selection.NumberFormat = "@"
Range("G8").Select
Columns("A:A").ColumnWidth = 54
Columns("A:A").Select
Selection.Replace What:=" [", Replacement:="[", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
ActiveCell.FormulaR1C1 = "gifts for new dads [390/mo - $0.97 - 1]"
Columns("A:A").Select
Selection.Replace What:=" [", Replacement:="[", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:A").Select
Selection.Replace What:=" - ", Replacement:="-", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="[", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("C2").Select
Columns("B:B").ColumnWidth = 19.86
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="]", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("H9").Select
ActiveWindow.SmallScroll Down:=-18
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Sort Number"
Range("A2").Select
Columns("A:A").ColumnWidth = 13.57
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "2"
Range("A4").Select
ActiveCell.FormulaR1C1 = "3"
Range("A5").Select
ActiveCell.FormulaR1C1 = "4"
Range("A6").Select
ActiveCell.FormulaR1C1 = "5"
Range("A2:A6").Select
Selection.AutoFill Destination:=Range("A2:A349")
Range("A2:A349").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "Keyword Phrase"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Searches Per Month"
Range("D1").Select
Columns("D:D").ColumnWidth = 12.86
Range("D1").Select
ActiveCell.FormulaR1C1 = "Average CPC"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Difficulty (0= easy, 1=difficult)"
Range("E2").Select
Columns("E:E").ColumnWidth = 28.71
Range("A1:E1").Select
Range("E1").Activate
Selection.Font.Bold = True
Range("E4").Select
ActiveWindow.SmallScroll Down:=-30
Range("A1:E1").Select
Range("E1").Activate
Selection.AutoFilter
Range("E3").Select
Columns("C:C").ColumnWidth = 21.29
Columns("D:D").ColumnWidth = 15.43
Columns("A:A").ColumnWidth = 15.71
Columns("E:E").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.ColumnWidth = 31
Range("E1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("E:E").ColumnWidth = 28.29
Columns("E:E").ColumnWidth = 29.71
Range("E2").Select
End Sub
Page updated
Report abuse