CRM/Lead Manager
      Back to home
      1. Internal RG Wiki
      2. Internal Documents
      3. CRM/Lead Manager

      Advanced CSV Formulas — Bulk Import

      Table of Contents

      Using Spreadsheet Formulas

      Formulas are expressions entered into a Cell that are used to manipulate the existing data within a spreadsheet. They perform a wide variety of tasks ranging from adding the value of a range, looking up a specific entry in a database, and even converting entered data into new formats.

      To enter a formula, you must begin with "=" to tell the spreadsheet you are making a calculation — rather than simply entering data. Formulas can run basic math functions (adding with "+", multiplying with "*", etc.) or use predefined formulas (adding with SUM(), multiplying with PRODUCT(), etc.). These formulas are not case sensitive, meaning that "sum()" does the same action as "SUM()" — although sum people prefer to use "all caps" to more clearly show the formulas being used.

       

      Cell Addresses

      To refer to existing data, enter in the address of the Cell (1 box) or Array (multiple boxes) you wish to use. Cell Addresses are formatted as ColumnRow (ex. "A1" for the cell in the first column and first row, "C2" for the cell in the third Column and second Row, etc.).

      Array Addresses are formatted using ":" between the boundaries of your Range, such as "A2:A4" used in the above example which selects "A2", "A3", and "A4". Leaving a Column or Row value missing or "open-ended" in the second boundary selects the entire Row or Column designated in the first boundary. "A2:A" would select every Cell in Column A except for A1, "A2:2" would select every Cell in Row 2.

       

      Useful Google Sheets Formulas

       

      Split Full Name to First Name/Last Name

      Behavior: Splits a single column of Names into 2 by the first space (" "), ignores blank rows, ignores additional spaces, Input must not contain "~".

      Input Range: "Full Name" in Column A

      Formula Destination: Row 1 with at least 2 empty columns

      Customization:

      • "$A$2:$A" may be changed to the Column that will be split (ex: "$C$2:$C" for splitting Column C)
      • The formula may be altered to split data on a different delimiter by changing " " inside the SUBSTITUTE() formula

       

      Merge Home Phone/Mobile Phone to Phone/Alternate Phone

      Behavior: Merges 2 columns of Phone Numbers into up to 2 columns, preferring "Mobile Phone", ensuring 1 Phone Number exists as "Phone"; creates "Primary Phone Type" column for clarity; ignores duplicate Phone Numbers.

      Input Range: "Mobile Phone" in Column D, "Home Phone" in Column E

      Formula Destination: "Phone" in F1, "Alternate Phone" in G1, "Primary Phone Type" in H1

      Customization:

      • "$D$2:$D" may be changed to the Column used as the preferred phone number
      • "$E$2:$E" may be changed to the Column used as the alternate phone number
      • "$F$2:$F" may be changed to the Column used as the "Phone" Formula Destination
      • "Mobile" or "Home" may be altered to label "Primary Phone Type" with different identifiers

       

      Grab Short Phone Numbers

      ={"Phone","Short Phone";ARRAYFORMULA(SPLIT(IF(LEN(REGEXREPLACE(TEXT($D$2:$D,"#"),"\(|\)| |-|\.",))>7,$D$2:$D,"|"&$D$2:$D),"|",0,0))}

      Behavior: Splits Phone Numbers into 2 columns, based on invalid (shorter than 8) length.

      Input Range: "Phone" in Column D

      Formula Destination: Row 1 with at least 2 empty columns

      Customization:

      • "$D$2:$D" may be changed to the "Phone" Column
      • ">7" can be adjusted for any Phone length

       

      Merge Date and Time Fields

      Behavior: Merges 2 columns of formatted Date (MM/DD/YYYY) and Time (HH:MM:SS) into 1 Date Time Column.

      Input Range: "Date" in Column A, "Time" in Column B

      Formula Destination: Row 2 of blank Column

      Customization:

      • "$A$2:$A" may be changed to the Column used as "Date"
      • "$B$2:$B" may be changed to the Column used as "Time"

       

      Fix Short Zip Codes

      Behavior: Appends Zip Codes shorter than 5 digits with up to 2 0s in front of the previous value, returns input value if longer than 5 digits or blank.

      Input Range: "Zip" in Column K

      Formula Destination: Row 1 of blank Column

      Customization:

      • "$K$2:$K" may be changed to the Column used as "Zip"

       

      FAQ

      • What are those "$" things in the Column addresses?
        • "$"s note Absolute References, meaning they do not move if the formula is filled into other Cells. These are optional relatively often.
      • My formulas stop working when I delete other columns! How do I keep them from changing?
        • Formulas are dynamic and will automatically update to adjust to new information. To prevent this and convert them to static data, simply select the entire array/column the formula is outputting to, then Copy (⌘+C) and Paste Values Only (Shift+⌘+V) into the same location. You may then delete the source information (such as "Full Name") if necessary,
      • Can I refer to a Cell, Column, or Row on another Tab?
        • Yes! By adding "TabName!" before the address (ex: TabName!$A$1:$A), the formula will refer to the Tab named "TabName". If the Tab's name includes a space, enclose the name in apostrophes (ex: 'Tab Name'!$A$1:$A)
      • How do I remove extra errors at the end of my formulas?
        • Add "IFERROR()" to the outside of your formula. A value may be defined in the event of an error message or it will be left blank if not defined.
      • I have a different problem. Can you write a formula for me?
        • Possibly — it depends on the issue. Feel free to reach out to Corey to discuss the idea!
      • I'm curious how these formulas work and am thinking about writing some new formulas. Can you explain how those formulas work?
        • Really? Awesome, read on!

       

      Full Name Formula Explained

       

      Let's break down the "Split Full Name to First Name/Last Name" formula to illustrate how these formulas can be used together to create exceptional results.

      SPLIT()

      We begin with "SPLIT()", which takes inputted Text or a Cell and breaks it into multiple new Cells based on a Delimiter (i.e. the character which marks on which the data should be split).

      Syntax: SPLIT(Input_Text, Delimiter, Split_On_Each, Remove_Empty_Space)

      • Split_On_Each: if True, divides text on every Delimiter character
      • Remove_Empty_Text: if False, allows empty cells between consecutive Delimiters

      In its basic form, we would most likely be using " " as a Delimiter, since most First and Last Names are already separated by a space.

      =SPLIT($A$2:$A," ",0,1)

      Now we have a formula that successfully splits one Cell into multiple by " ". However, we have more than one Cell of data we want to split.

      ARRAYFORMULA()

      Certain formulas (like SPLIT()) only work on a single Cell by default, even if you enter an entire Column as the target input range. To extend this formula to affect multiple Cells, we can nest it inside of "ARRAYFORMULA()". ARRAYFORMULA() allows formulas to run multiple times across a wider range, in this case having "SPLIT()" run on each cell in Column A.

      =ARRAYFORMULA(SPLIT($A$2:$A," ",0,1))

      Now we have the formula running across the entire column — but that has revealed a couple of issues we will need to address:

      1. Some Full Names have multiple spaces, but we only want 2 Columns for First Name and Last Name
      2. Some Full Names are blank, resulting in "#VALUE" errors at the bottom of our sheet

      SPLIT() works by dividing text on every matching Delimiter it finds and can't stop itself after the first new column. To work around this limitation, we can instead give it a unique Delimiter that will only occur once, then split on that.

      SUBSTITUTE()

      SUBSTITUTE() looks inside of the inputted Text and replaces a specific target character (or string) with a new value. It's useful for removing abbreviations, changing verbiage, or excluding special characters, but in this case, we're using it because it allows us to specify how many times to do its job.

      Syntax: SUBSTITUTE(Input_Text, Search_for, Replace_with, Occurrence_Number)

      • Search_for: what text to remove
      • Replace_with: what text to add in its place
      • Occurence_Number: whether to look for a specific instance of the Delimiter; if Blank, replaces all

      We originally wanted to use " " as our Delimiter, but unfortunately, we've seen certain Full Names with multiple spaces. To address this, we can SUBSTITUTE() out the first space with a new Delimiter.

      We'll use "~" as a new Delimiter, as it typically is not present in Full Names. Keep in mind, we will have to update our previous SPLIT() formula to look for "~" instead of " ".

      Now our formula successfully limits itself to 2 columns — but we still have those "#VALUE" errors at the bottom of our Column — since we can't SPLIT() a blank Cell. We could simply nest the whole formula inside of IFERROR() to remove all errors, but this could prevent us from troubleshooting other bugs we may not have anticipated. Instead, we can limit this formula to only run if there is a Full Name to split.

      IF()

      IF() uses a logical expression to dictate different results depending on whether the given expression is True or False. It is a very flexible formula that is useful for when you have multiple possibilities of inputted data or resulting calculations.

      Syntax: IF(Logical_Expression,Value_if_True,Value_if_False)

      In this case, we want to only run our formula if the Full Name is not blank. We can then insert this logic check before our formula attempts to SPLIT() our Full Name column.

      Now, that looks a lot like what we're looking for! We just have one last finishing touch to add to this formula for a more polished result.

      {LITERAL_ARRAY}

      Okay, this is where things can seem a little scary. Using {}, you can create multiple Columns and Rows inside of a single Cell. Using this feature, we can create the "First Name" and "Last Name" headers in Row 1.

      When inside of {}, you can specify new Columns with "," and new Rows with ";". In this case, we can simply encase our entire formula in {}, then enter in a new Header row at the top. Keep in mind, since we're adding in a Header row in our formula, we'll have to move our formula from Row 2 to Row 1.

      Example Sheet