Excel Tips & Tricks

    Name Header and Format


     The Name field is the only field that is required to process an import file into Clockwork. The First Name, Middle Name, Last Name fields are concatenated (condensed) together into a single name string during the import process.  You will need to have the full name as a single field in "First Last" formatIf you have the name in the format of “Last, First" format, it will need to be reformatted to the “First Last” sequence. To do this in Excel or Google Sheets:

    1. Create 2 additional columns (named First Name and Last Name)  

    2. In the First Name column, insert the equation: =RIGHT(C2,LEN(C2)-FIND(",",C2,1)-1), where C2 is the field with the name.  

    3. In the Last Name column, insert the equation: =LEFT(C2,FIND(",",C2,1)-1), where C2 is the field with the name. 

    4. Select All the records in the first name and last name fields.  Copy and Paste as Value to replace the equations with the actual values. 

    5.  Delete the Original name field.

     **NOTE: If you do not modify your names, they will be imported with the format (last name, first name) – this is NOT an error. Unless you are consistent, however, you will end up with contacts in different formats in your database. 

       

    How to identify duplicates

     To identify duplicates, put values that can be used to identify duplicates (unique characters) at the front

    How to convert names in different fields or change the order of names

    To quickly split the first name and the last name and get these in separate cells, you can use the Excel  text-to-columns feature. This will instantly give you the results with the first name in one column and last name in another column:
      Select the data set. 
      Go to  Data →  Data Tools →  Text to Columns. This will open the  Convert Text to Columns  Wizard. 
      In Step 1, make sure  Delimited is selected (which is also the default selection). Click on Next.


      In Step 2, select ‘Space’ as the delimiter. If you suspect that there could be double/triple consecutive spaces between the names, also select ‘Treat consecutive delimiters as one’ option. Click on Next.


      In Step 3, select the destination cell. If you don’t select a destination cell, it would overwrite your existing data set with the first name in the first column and last name in the adjacent column. If you want to keep the original data intact, either create a copy or choose a different destination cell.
      Next, click on Finish.

    Deduplication Efforts Avoid duplicate data. Put values that can be used to identify duplicates (unique characters) at the front. Clockwork automatically merges data based on LinkedIn URL or email address ONLY. This means that if your import data does not have an email address or LinkedIn URL, a new  person   record will be created and will NOT auto-merge if that person already exists in your database. 

       

    Project Candidacies 

    The import process needs some way to identify an existing contact in Clockwork or have enough contact information in the source file to create a contact to reference in the candidacy information. A contact can be created with as little as a Name column. If a unique identifier is used (person_id, external_ref, email, LinkedIn URL) and an existing contact is found with this value, then the candidacy will be related to the existing candidate. 

    To import candidacy information, some identifier for a person must be included in the source file. 

    Tags

    Split special data from contact information: We allow you to import notes and tags with contact information. Consider splitting this information into a separate data load (include  contact  name and the note or tags), as these fields of information are generally responsible for most of the import errors that are experienced. 

    Use semicolons (;) to separate tags in the tag column, if there are more than one tag per person. Tags should not include commas and other special characters. 

    Individual tags cannot be more than 30 characters in length. (this is an upload constraint – tags can manually be created, or altered, to be longer than this when created in the app, but are limited with the CSV file import to 30 characters). 

       

    How to find the length of text in a cell

    To count the characters in cells, you can use the Excel   LEN function which returns the number of characters in a text string.

    To use the function, enter =LEN(  cell) in the formula bar, then press Enter on your keyboard.   Eg.  =LEN(A1) 

       

    How to convert names to proper formatting

    Contact names should be in proper formatting (Uppercase first letter, lowercase remaining letters in the name). You can format names in proper formatting in Excel by using the PROPER function. To do this in Excel or Google Sheets:

    1. Add an additional column.
    2. Insert the equation: =PROPER(TEXT), where TEXT is a reference to the field where the contact name is.
    3. Copy this equation for all values in the contact name rows.
    4. Select All the records in the first name and last name fields. Copy and Paste as Value to replace the equations with the actual values.
    5. Delete the original name field.
Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.