Excel Cheat Sheet for Keyword Marketers

Here’s a list of some of the less well known Excel formulas and macros 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 designed for keyword marketers, but useful for anyone wanting to grow their Excel bag of tricks.

Enjoy!

FORMULAS

  1. Get domain from URL
  2. Get subdomain from URL
  3. Remove first x characters from cell
  4. Remove last x characters from a cell
  5. Group keyword phrases automatically based on words they contain
  6. Word count
  7. Find out if a value exists in a range of other values
  8. Get true or false if a word or string is in a cell
  9. Remove first word from cell (all before & including 1st space)
  10. Replace the first word in a cell with another word
  11. Super trim – more reliable trimming of spaces from cells
  12. Perform text-to-columns using a formula
  13. Extract the final folder path from a URL
  14. Extract the first folder path from a URL
  15. Remove all text after the xth instance of a specific character
  16. Create an alphabetical list of column letters for use in other formulas
  17. Count instances of a character in a cell
  18. Count the number of times a specific word appears in a cell
  19. Return true if there are no numbers in a cell
  20. Get the current column letter for use in other formulas
  21. Put your keywords into numbered batches for pulling seasonal search volume data
  22. Word order flipper
  23. Find the maximum numerical value in a row range, and return the column header
  24. Find position of nth occurrence of character in a cell

VBA

  1. Convert all non-clickable URLs in your spreadsheet to clickable hyperlinks
  2. Conditional formatting by row value
  3. Remove duplicates individually by column
  4. Merge adjacent cells in a range based on identical value
  5. Remove all instances of any text between and including 2 characters
  6. Highlight mis-spelled words
  7. Lock all slicers in position
  8. Split delimited values in a cell into multiple rows with key column retained
  9. Make multiple copies of a worksheet at once
  10. Add a specific number of new rows based on cell value
  11. Column stacker
  12. Superfast find and replace for huge datasets
  13. Paste all cells as values in a worksheet in the active range
  14. Format all cells to any format without having to select them
  15. Formula activation – insert equals at the beginning for a range of cells
  16. Consolidate all worksheets from multiple workbooks into one workbook
  17. Fast deletion of named columns
  18. Find and replace based on a table

 

Get domain from URL:

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

This works by bringing back everything to the left of the first trailing slash found after the initial 2 in ‘http..://’, which in a URL is the slash occurring after the TLD.

 

Get subdomain from URL:

=IF(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A2,FIND(".",A2)),"http://",""),".",""),"https://",""),"domain","")="","none",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A2,FIND(".",A2)),"http://",""),".",""),"https://",""),"domain",""))

When you just need the subdomains in a big list from a bunch of differently formatted URLs. This formula works regardless of the presence of the protocol. What it lacks in elegance, it more than makes up for in usefulness.

 

Remove first X characters from cell:

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

If there’s something consistent that you want to remove from the front of data in your cells, such as an html tag like <title>, you can use this to remove it by specifying its length in characters in this formula, so X would be 7 in this case.

 

Remove last X characters from a cell:

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

You might use this to remove the trailing slash from a list of URLs, for example, with X as 1.

 

Group keyword phrases automatically based on words they contain:

=IFERROR(LOOKUP(2^15,SEARCH($C$2:$C$200,A2),$D$2:$D$200),"/")

This little chap deserves a blog post all its own. Here’s what it does:

Example: Bulk categorisation of keywords by colour and hair type groups.

Using the formula to group your keywords:

      • $C$2:$C$200 is your string-to-search-for range (the list of all the possible words you want to check for in the keyword).
      • $D$2:$D$200 is your Label to return when string found, put it in the next column along lined up (this can just be the word you’re checking for if you want – same as above)
      • A2 is the cell containing the keyword string which you are searching to see if it contains any of the listed strings so you can label it as such
      • “/” is what gets returned when none of the strings are matched

Using the formula

Word count:

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

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

 

Find out if a value exists in a range of other values:

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

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. It gives TRUE or FALSE.

 

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

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

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.

 

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.

 

Replace the first word in a cell with another word:

=REPLACE(A2,1,LEN(LEFT(A2,FIND(" ",A2)))-1,"X")

“X” is the word you want to replace the incumbent first word with, or this can be a cell reference.

 

 

Super trim – more reliable trimming of spaces from cells:

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

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

 

Perform text-to-columns using a formula:

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

This is handy for template building. It provides a way of doing text-to-columns automatically with formulas, using a delimiter you specify. In the example, space ” ” is used as the delimiter.

 

 

Extract the final folder path from a URL:

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

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

 

Extract the first folder path from a URL:

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

Good for extracting language folder.

 

Remove all text after the Xth instance of a specific character:

=LEFT(A2,FIND(CHAR(160),SUBSTITUTE(A2,"/",CHAR(160),LEN(A2)-LEN(SUBSTITUTE(A2,">",""))-0)))

Say you want to chop the last folder off a URL, or revert a keyword cluster to a previous hierarchy level. The “/” is the character where the split will occur, change it to whatever you want. The “-0” at the end chops off everything after the last instance. Changing it to -1 would chop off everything after the penultimate instance, and so on.

 

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,"")

Unlike with numbers, Excel doesn’t automatically give you the next letter of the alphabet if you drag down after selecting cells with ‘a’ and ‘b’ but you can use this to achieve that effect. It runs through the columns, so it will keep working past Z, giving you AA and AB etc. That’s handy for making indirect references in formulas.

 

Count instances of a character in a cell:

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

Countif “*”&”x”&”*” doesn’t cut it for this task because it counts cells, not occurrences. The example here is for ” ” space character.

 

Count the number of times a specific word appears in a cell:

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

The formula above works for individual characters, but if you need to count whole words this will work – handy for checking keyword inclusion in landing page copy for SEO. In the example, B2 should contain the word you are counting the instances of within A2.

 

Return TRUE if there are no numbers in a cell:

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

Change the end to >0 to show TRUE if there are numbers present. Handy for isolating and removing cells of data which can be identified as unwanted by the presence or absence of a number, such as a mix of item names and item product codes when you only want the item names.

 

Get the current column letter for use in other formulas:

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

If you’re using indirect references and want a fast way to just get the current column letter placed into your formula, use this.

 

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

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

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

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

Turns ‘dresses white wedding’ into ‘white wedding dresses’. Use it in steps inside itself to further rearrange words in a different order.

 

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

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

So if your column headers are months or categories, this brings back which one contains the highest value for that row. Useful for showing which month has the highest search volume for a keyword.

 

Find position of nth occurrence of character in a cell:

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

Useful as a part of other formulas.

 

 

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

 

Lock all slicers in position:

If you send Excel documents to clients with slicers in them, you might worry that they’ll end up moving the slicers around while trying to use them – a poor experience which makes your document feel less professional.  But there’s a way around it – run this code and your slicers will be locked in place across all worksheets, while still operational. This effect persists when the document is re-saved as a normal .xlsx file.

Option Explicit

Sub DisableAllSlicersMoveAndResize()

 Dim oSlicerCache As SlicerCache
 Dim oSlicer As Slicer
 
 For Each oSlicerCache In ActiveWorkbook.SlicerCaches
 For Each oSlicer In oSlicerCache.Slicers
 oSlicer.DisableMoveResizeUI = True
 Next oSlicer
 Next oSlicerCache
 
End Sub

 

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:

The post Excel Cheat Sheet for Keyword Marketers appeared first on FOUND.