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.
Get domain from URL:
Remove first 3 characters from cell:
Remove last 1 character from a cell:
Label keywords based on any identifying words (strings) they contain:
- $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
See how many words are in your keyword to identify if it’s long tail and get a measure of potential intent.
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.
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:
Remove first word from cell (all before & including 1st space):
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:
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):
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:
Get the first folder from a URL:
Good for extracting language folder:
Create an alphabetical list of column letters for use in other formulas A,B,C…AA,BB etc:
Count the number of given character instances in a cell:
For when countif “*x*” doesn’t cut it:
example is for ” ” space character
Show if there are no numbers in a string:
Change the end to >0 to reverse it.
Get the current column letter for use in other formulas:
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.
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)
Find position of nth occurrence of character in a cell:
EXCEL VBA MODULES
To use these:
- Save your workbook as .xlsm
- Reopen it and hit alt + f11
- In the menu, insert > module
- Paste in the code
- 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
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
- 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: