Corporate Website

Refining your patent data with OpenRefine – Part 2

26th, October, 2018

So For this, we will just export our refined and cleaned sheet from OpenRefine in excel Format and will use the below VB Excel function to concatenate the data back into single cell.

Now to concatenate all these cleaned inventors name, write down the below code as a macro. You can press Alt+F11 to open the VB code sheet. Or can also access it from the developer tab.
Insert Module and paste the code

VB Code:
Function ConcatAD(a As Range, b As String) As String
Dim cr As String
Dim x As String
cr = “”
For Each cell In a
If cell <> “” Then
x = cell & b
cr = cr & x
End If
Next cell
cr = Left(cr, Len(cr) – Len(b))
ConcatAD = cr
End Function

This code will create a function in your excel sheet with function name as ‘ConcatAD’ (minimize the VB sheet and get back to the excel sheet

The format to use this function is

=ConcatAD(ColumnRange, “seperator”) something like =ConcatAD(A2:F2,” | “)

Then just drag drop the formula and we are good to go, with all our data sorted and cleaned for our accurate Patent analytics

These are some more useful links to know and learn more about OpenRefine
Open Refine Wiki Recipes
Open Refine Tips and Tricks