Converting Formula to Column

When you import your data, there is often a necessity to process imported values in some way. For example, you may need to split a name value into two fields. Such operations cannot be performed during the primary import, but it is possible to accomplish this task with the help of formulas.

Using formulas can be very helpful when importing files into columns. To convert imported values into the desired format, follow these steps:

  1. Import a field value in the initial format.

  2. Create a formula to perform the necessary transformation. After the transformation has been executed, the column will contain the desired value.

  3. Convert the column of formula type to a regular column of the same type (for example, convert a Formula-User column type to a User column type).

  4. Delete the column with the initially imported values.

Example 1: First Name and Last Name

Let’s say you have a Name column where you store the first and last names of your clients in a simple text format. However, for some future manipulations, you need to store them in two separate columns: First Name and Last Name. Here’s what you should do:

  1. Create a formula-text column called First Name. The formula should be: Left([Name], " ").

  2. Create a formula-text column called Last Name. The formula should be: NotLeft([Name], " ").
    Thus, data from the Name column are duplicated and divided into two additional fields: First Name and Last Name. Once the two new columns are ready, check if views and reports represent the data correctly in the way you need.

  3. Change the type of the First Name and Last Name columns to regular text columns. At this step, all data taken from the original Name column are recorded into the newly created columns.

  4. Now, if necessary, you can change the type of the Name column to a formula-text column. The formula should be: List(" ", [First Name], [Last Name]).

Example 2: Importing Duration

Suppose you need to process an imported value of the Duration type. It is not possible to obtain the desired result immediately after importing because you do not know the measurement units (days, hours, minutes, seconds). Follow these steps:

  1. Import a field into a column of the Numeric type.

  2. Create a formula that processes the imported value, for example: Days([Field]), Hours([Field]), Minutes([Field]), Seconds([Field]).

  3. Convert the received Formula-Duration column type into a simple Duration type.

  4. Delete the column with the initially imported values.