Excel Cheat Sheet for Keyword Marketers

27th November 2018 - 21 minutes read  Data & AnalyticsTips & Tricks
Richard Lewis - SEO Lead

Excel Formulas & VBA for Keyword Marketers

Here’s a list of some of the less well known Excel formulas and VBA modules that regularly come in handy for keyword marketers. That could be SEOs, PPCs or anyone who works with large spreadsheets containing keywords and associated data like search volume, CPC & categories. Think of it as an excel cheat sheet for marketers.

 

 

Enjoy!

Get domain from URL:

=LEFT(B2,FIND("/",B2,9))

 

Remove first 3 characters from cell:

=RIGHT(A1,LEN(A1)-3)

 

Remove last 1 character from a cell:

=LEFT(B2,LEN(B2)-1)

 

Label keywords based on any identifying words (strings) they contain:

=IFERROR(LOOKUP(2^15,SEARCH($K$6:$K$200,B5),$L$6:$L$200),"/")
  • $K$6:$K$20 is your string-to-search-for range
  • $L$6:$L$200 is your Label to return when string found, put it in the next column along lined up.
  • B5 is the cell containing the keyword string which you are searching for any of the listed strings in order to label it
  • “/” is what gets returned when none of the strings are matched

Using the formula

 

Word count:

See how many words are in your keyword to identify if it’s long tail and get a measure of potential intent.

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)

 

The simplest way to get TRUE / FALSE for ‘value in list’ or ‘not in list’, instead of using vlookup:

This is my favourite, so often we just need to know if URLs in list A are contained within list B. No need to count vlookup columns or iferror.

=ISNUMBER(MATCH(P3,B:B,0))

 

Get TRUE or FALSE if a word or string is in a cell:

If you fancy a break from using the ‘contains’ filter, this can be a way to get things done faster and in a more versatile way:

=ISNUMBER(SEARCH("text-to-find",A2))

 

Remove first word from cell (all before & including 1st space):

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

To remove the last word instead, just use LEFT instead of RIGHT.

 

Super trim – more reliable trimming of spaces from cells:

Sometimes using =TRIM() fails because of an unconventional space character from something we’ve pasted into Excel. This gets them all:

=TRIM(SUBSTITUTE(B2,CHAR(160),CHAR(32)))

 

Text to columns with space as the delimiter (can be anything) using a draggable formula instead, so it can be used in a template:

=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),((COLUMNS($A2:A2)-1)*LEN($A2))+1,LEN($A2)))

 

Get subdomain from URL (you need to edit the domain name, ialbatros):

=IF(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(B11,FIND(".",B11)),"http://",""),".",""),"https://",""),"ialbatros","")="","none",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(B11,FIND(".",B11)),"http://",""),".",""),"https://",""),"ialbatros",""))

 

Get the last path from a URL (everything after the last slash, not counting the trailing slash if present):

Good for when you need to get just the last portion of a URL, that pertains to the specific page:

=IF(AND(LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))=3,RIGHT(A2,1)="/"),"",IF(RIGHT(A2,1)="/",RIGHT(LEFT(A2,LEN(A2)-1),LEN(LEFT(A2,LEN(A2)-1))-FIND("@",SUBSTITUTE(LEFT(A2,LEN(A2)-1),"/","@",LEN(LEFT(A2,LEN(A2)-1))-LEN(SUBSTITUTE(LEFT(A2,LEN(A2)-1),"/",""))),1)),RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))),1))))

 

Get the first folder from a URL:

Good for extracting language folder:

=IF(LEN(E15)-LEN(SUBSTITUTE(E15,"/",""))>3,LEFT(RIGHT(E15,LEN(E15)-FIND("/",E15,9)),FIND("/",RIGHT(E15,LEN(E15)-FIND("/",E15,9)))-1),RIGHT(E15,LEN(E15)-FIND("/",E15,9)))

 

 

Create an alphabetical list of column letters for use in other formulas A,B,C…AA,BB etc:

=SUBSTITUTE(ADDRESS(1,ROWS(A$1:A1),4),1,"")

 

Count the number of given character instances in a cell:

For when countif “*x*” doesn’t cut it:

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))

example is for ” ” space character

 

Show if there are no numbers in a string:

Change the end to >0 to reverse it.

=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},B28))<1

 

Get the current column letter for use in other formulas:

=MID(ADDRESS(ROW(),COLUMN()),2,SEARCH("$",ADDRESS(ROW(),COLUMN()),2)-2)

 

Put your keywords into numbered batches for pulling seasonal search volume data:

To save you having to count out 2,500 keywords each time. This batches them up so you just have to filter for the batch number, ctrl A, ctrl C, ctrl V.

=IF(A2=43,1,A2+1)

43 is the number of keywords in your list divided by 2500, which is the keyword planner limit

Use the blank row insertion macro to make the batches easily selectable

 

Word order flipper:

Excel is not built for string parsing and manipulation, but if you have to do it in Excel you can try this. Turns ‘dresses white wedding’ into ‘white wedding dresses’. Use it in steps inside itself to further rearrange words in a different order.

=TRIM(MID(F18,SEARCH(" ",F18)+1,250))&" "&LEFT(F18,SEARCH(" ",F18)-1)

 

Find the maximum numerical value in a row range, and return the column header:

Useful for when you need to know which month has the highest search volume (a2:f2 = the values, a1:f1 = the headers)

=INDEX($A$1:$F$1,MATCH(MAX(A2:F2),A2:F2,0))

 

Find position of nth occurrence of character in a cell:

=FIND(CHAR(1),SUBSTITUTE(A1,"c",CHAR(1),3))

 

 

EXCEL VBA  MODULES

To use these:

  1. Save your workbook as .xlsm
  2. Reopen it and hit alt + f11
  3. In the menu, insert > module
  4. Paste in the code
  5. Press the play button

There’s no need to understand the code. But be careful to save a backup copy of your workbook before running any of these – they can’t be undone with ctrl + z!

 

Convert all non-clickable URLs in your spreadsheet to  clickable hyperlinks:

So you can visit the URLs easily if you need to e.g. for optimisation of a lot of pages, so you don’t have to mess about double clicking each one to get it ready.

Sub HyperAdd()

For Each xCell In Selection
 ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
 Next xCell

End Sub

 

Conditional formatting by row value:

So the colour intensity is relative to each row only, rather than the entire range. You need to use this to complete the search landscape document seasonality tab.

Sub NewCF()
 Range("B1:P1").Copy
 For Each r In Selection.Rows
 r.PasteSpecial (xlPasteFormats)
 Next r
 Application.CutCopyMode = False
 End Sub

 

Remove duplicates individually by column:

If you have a lot of columns, each of which needs duplicates removing individually e.g. if you have a series of category taxonomies to clean – you can’t do this from the menu:

Sub removeDups()
 Dim col As Range
 For Each col In Range("A:Z").Columns
 With col
 .RemoveDuplicates Columns:=1, Header:=xlYes
 End With
 Next col
 End Sub

 

Merge adjacent cells in a range based on identical value:

To save you doing it individually when you need to make a spreadsheet look good:

Sub MergeSameCell()
 'Updateby20131127
 Dim Rng As Range, xCell As Range
 Dim xRows As Integer
 xTitleId = "KutoolsforExcel"
 Set WorkRng = Application.Selection
 Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
 Application.ScreenUpdating = False
 Application.DisplayAlerts = False
 xRows = WorkRng.Rows.Count
 For Each Rng In WorkRng.Columns
 For i = 1 To xRows - 1
 For j = i + 1 To xRows
 If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then
 Exit For
 End If
 Next
 WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge
 i = j - 1
 Next
 Next
 Application.DisplayAlerts = True
 Application.ScreenUpdating = True
 End Sub

 

Remove all instances of any text between and including 2 characters from a cell (in this example, the < and >):

Especially good for removing HTML tags from screaming frog extractions, kind of a stand in for regex.

Public Function DELBRC(ByVal str As String) As String
 While InStr(str, "<") > 0 And InStr(str, ">") > InStr(str, "<")
 str = Left(str, InStr(str, "<") - 1) & Mid(str, InStr(str, ">") + 1)
 Wend
 DELBRC = Trim(str)
 End Function

 

Highlight mis-spelled words:

This can help you identify garbled / nonsense keywords from a large set, or just to spellcheck in Excel if you need to.

Sub Highlight_Misspelled_Words()
 For Each cell In ActiveSheet.UsedRange
 If Not Application.CheckSpelling(Word:=cell.Text) Then cell.Interior.ColorIndex = 3
 Next
 End Sub

 

Slice’n dice – Split delimited values in a cell into multiple rows with key column retained:

It’s easy to put a delimited string (Keyword,Volume,CPC…) into columns using text-to-columns but what if you want it split vertically instead, into rows? This can help:

Sub SliceNDice()
 Dim objRegex As Object
 Dim X
 Dim Y
 Dim lngRow As Long
 Dim lngCnt As Long
 Dim tempArr() As String
 Dim strArr
 Set objRegex = CreateObject("vbscript.regexp")
 objRegex.Pattern = "^\s+(.+?)$"
 'Define the range to be analysed
 X = Range([a1], Cells(Rows.Count, "b").End(xlUp)).Value2
 ReDim Y(1 To 2, 1 To 1000)
 For lngRow = 1 To UBound(X, 1)
 'Split each string by ","
 tempArr = Split(X(lngRow, 2), ",")
 For Each strArr In tempArr
 lngCnt = lngCnt + 1
 'Add another 1000 records to resorted array every 1000 records
 If lngCnt Mod 1000 = 0 Then ReDim Preserve Y(1 To 2, 1 To lngCnt + 1000)
 Y(1, lngCnt) = X(lngRow, 1)
 Y(2, lngCnt) = objRegex.Replace(strArr, "$1")
 Next
 Next lngRow
 'Dump the re-ordered range to columns C:D
 [c1].Resize(lngCnt, 2).Value2 = Application.Transpose(Y)
 End Sub

 

Make multiple copies of a worksheet at once:

If you are making a reporting template for example, and want to get the sheets for all 12 weeks created in one go:

Sub swtbeb4lyfe43()

ThisWS = "name-of-existing-worksheet"
 '# of new sheets
 s = 6
 '# of new sheets
 For i = 2 To s
 Worksheets("name-of-existing-worksheet-ending-with-1").Copy After:=Worksheets(Worksheets.Count)
 ActiveSheet.Name = ThisWS & i
 Next i
 End Sub

 

Add a specific number of new rows based on cell value:

Saves repeatedly using insert row, pressing F4 etc:

Sub test()
 On Error Resume Next
 For r = Cells(Rows.Count, "E").End(xlUp).Row To 2 Step -1
 For rw = 2 To Cells(r, "E").Value + 1
 Cells(r + 1, "E").EntireRow.Insert
 Next rw, r
 End Sub

 

Column stacker:

This one’s great when you have lots of columns of information that you want to be combined all into one master column:

Sub ConvertRangeToColumn()
 'UpdatebyExtendoffice
 Dim Range1 As Range, Range2 As Range, Rng As Range
 Dim rowIndex As Integer
 xTitleId = "KutoolsforExcel"
 Set Range1 = Application.Selection
 Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8)
 Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8)
 rowIndex = 0
 Application.ScreenUpdating = False
 For Each Rng In Range1.Rows
 Rng.Copy
 Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
 rowIndex = rowIndex + Rng.Columns.Count
 Next
 Application.CutCopyMode = False
 Application.ScreenUpdating = True
 End Sub

 

Superfast find and replace for huge datasets:

To match partial cell, change to ‘X1Part”.

Sub Macro1()

Application.EnableEvents = False
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual

' fill your range in here
 Range("S2:AJ252814").Select
 ' choose what to search for and what to replace with here
 Selection.Replace What:="0", Replacement:="/", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Application.EnableEvents = True
 Application.ScreenUpdating = True
 Application.Calculation = xlCalculationAutomatic
 Application.CalculateFull

End Sub

 

Paste all cells as values in a worksheet in the active range:

For when your spreadsheet is too slow to do it manually

Sub ToVals()
 With ActiveSheet.UsedRange
 .Value = .Value
 End With
 End Sub

 

Format all cells to general format or whatever you like, without having to select them:

Another good one for when your spreadsheet is too slow.

Sub dural()
 ActiveSheet.Cells.NumberFormat = "General"
 End Sub

 

Formula activation – Insert equals at the beginning for a range of cells:

If you’re making something complex with a lot of formulas that you don’t want switched on yet, but you want to be able to use other formulas at the same time (i.e. can’t turn off calculations) this can help. It’s also good for just adding things to the start of cells:

Sub Insert_Equals()

Application.ScreenUpdating = False

Dim cell As Range

For Each cell In Selection
 cell.Formula = "=" & cell.Value
 Next cell

Application.ScreenUpdating = True

End Sub

 

Consolidate all worksheets from multiple workbooks in a folder on your computer into a single workbook with all the worksheets added into it:

If you have a big collection of workbooks which you want consolidated into one, you can do it in a single step using this macro. Especially good for when the workbooks you need to consolidate are big and slow.

Sub CombineFiles()

Dim Path As String
 Dim FileName As String
 Dim Wkb As Workbook
 Dim WS As Worksheet

Application.EnableEvents = False
 Application.ScreenUpdating = False
 Path = "C:\scu" 'Change as needed
 FileName = Dir(Path & "\*.xl*", vbNormal)
 Do Until FileName = ""
 Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
 For Each WS In Wkb.Worksheets
 WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
 Next WS
 Wkb.Close False
 FileName = Dir()
 Loop
 Application.EnableEvents = True
 Application.ScreenUpdating = True

End Sub

 

Fast deletion of named columns in a spreadsheet which is responding slowly:

Sometimes, one does not simply ‘delete a column’. This is for those times.

Sub Delete_Surplus_Columns()

Dim FindString As String
 Dim iCol As Long, LastCol As Long, FirstCol As Long
 Dim CalcMode As Long

With Application
 CalcMode = .Calculation
 .Calculation = xlCalculationManual
 .ScreenUpdating = False
 End With

FirstCol = 1
 With ActiveSheet
 .DisplayPageBreaks = False

LastCol = .Cells(3, Columns.Count).End(xlToLeft).Column

For iCol = LastCol To FirstCol Step -1

If IsError(.Cells(3, iCol).Value) Then
 'Do nothing
 'This avoids an error if there is a error in the cell
 ElseIf .Cells(3, iCol).Value = "Value B" Then
 .Columns(iCol).Delete
 ElseIf .Cells(3, iCol).Value = "Value C" Then
 .Columns(iCol).Delete
 End If

Next iCol

End With

With Application
 .ScreenUpdating = True
 .Calculation = CalcMode
 End With

End Sub

 

Find and replace based on a table in another worksheet:

Use X1Part for string match & replace within a cell, or X1 whole for whole cell match & replace:

Sub Substitutions()

Dim rngData As Range
 Dim rngLookup As Range
 Dim Lookup As Range

With Sheets("Sheet1")
 Set rngData = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
 End With

With Sheets("Sheet2")
 Set rngLookup = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
 End With

For Each Lookup In rngLookup
 If Lookup.Value <> "" Then
 rngData.Replace What:=Lookup.Value, _
 Replacement:=Lookup.Offset(0, 1).Value, _
 LookAt:=xlWhole, _
 SearchOrder:=xlByRows, _
 MatchCase:=False
 End If
 Next Lookup

End Sub

 

BONUS TIPS

  • If you have a slow spreadsheet that’s locked up Excel while it;s calculating, but you still need to use Excel for other stuff, you can open a completely new instance of Excel by holding Alt, clicking Excel in the taskbar, and answering ‘yes’ to the pop up box. This isn’t just a new worksheet – it’s a totally new instance of Excel.
  • To open a new workbook in the same instance of Excel a bit more quickly than usual when you already have workbooks open, you can use a single middle mouse click on Excel in the taskbar.

 

 

 

We’ve written some other blog posts about excel, you can find them here: