Returning Specific Characters in a Field

Problem

In some cases, you may want to obtain a specific segment from a field.

For example, you may want to obtain the last few characters of fixed length from a string. The following figure shows the effect.

Solution

You can use the Split Field function to split the field with one click, or use the text functions to obtain specific characters from a field.

Procedure

You can download the sample data Supplier Info.xlsx.

Method One

Choose More > Split Field on the data editing page to enter the function page, as shown in the following figure.

Split the field by the separator (-) and enter 2 in Split Result, as shown in the following figure.

Method Two

Taking the RIGHT function as an example, you can obtain the last eight digits of Phone Number to create the RIGHT Function field.

1. Create an analysis subject with the sample data Supplier Info, click Field Settings, and select the Contact Name and Phone Number fields, as shown in the following figure.

2. Click Formula Column and add the RIGHT Function column using the RIGHT function. The obtained column returns the last eight digits of phone numbers, as shown in the following figure.

The following table introduces the functions.

Effect Display

In this case, you can obtain the last few characters of fixed length from a string. The following figure shows the effect.

You can use the LEFT and FIND functions. The FIND function can locate a character in a string.

You can download the sample data Field Interception.xlsx.

Procedure

For example, you can obtain user accounts and create the LEFT Interception (Unfixed) field from the User Account and Password field.

1. Create an analysis subject with the downloaded Field Interception table. All fields are selected by default.

2. Click Formula Column, name the column LEFT Interception (Unfixed), enter the formula LEFT(User Account and Password,FIND(":",User Account and Password)), and click OK, as shown in the following figure.

Note:

If you do not need colons (:) in the field, you can modify the formula to LEFT(User Account and Password,FIND(":",User Account and Password)-1).

The following table explains the formula.

Effect Display

In this case, you can obtain all characters from the first character to the colon (:) in the string, as shown in the following figure.

You can use the RIGHT, FIND, and LEN functions.

You can use the sample data in section "Returning All Characters Before a Specific Character."

Procedure

Method One

1. Choose More > Split Field on the data editing page to enter the function page, as shown in the following figure.

Split the field by the separator (:) and enter 2 in Split Result, as shown in the following figure.

Method Two

You can obtain the passwords from the User Account and Password field and create the Password column.

Click Formula Column, name the column Password, enter the formula RIGHT(User Account and Password,LEN(User Account and Password)-FIND(":",User Account and Password)), and click OK, as shown in the following figure.

The following table explains the formula.

Effect Display

In this case, you can obtain all characters after the colon (:), as shown in the following figure.

You can download the sample data Field Interception (Unfixed Length).xlsx.

You can obtain the passwords from the middle of the User Account and Password field, and create a column.

Procedure

Method One

Choose More > Split Field on the data editing page to enter the function page, as shown in the following figure.

Split the field by the separator (:) and enter 2 in Split Result, as shown in the following figure.

Repeat the above steps and split the User Account and Password-2 field by the comma (,), as shown in the following figure.

Method Two

1. Create an analysis subject with the downloaded Field Interception (Unfixed Length) table. All fields are selected by default.

2. Click Formula Column, name the column LEFT Interception (Unfixed Length), enter the formula MID(User Account and Password,FIND(":",User Account and Password)+1,FIND(",",User Account and Password)-FIND(":",User Account and Password)-1), and click OK, as shown in the following figure.

The following table explains the formula.

Effect Display

In this case, you can obtain all characters between the colon (:) and the comma (,), as shown in the following figure.

You can download the sample data Text Interception.xlsx.

Procedure

1. Create an analysis subject with the downloaded Text Interception table. All fields are selected by default.

2. Click Formula Column, name the column Remove the Suffix, and enter the formula LEFT(Company,LEN(Company)-6), and click OK, as shown in the following figure.

The following table explains the formula.

Effect Display

In this case, the Office suffix is removed from the field and only the company names are saved, as shown in the following figure.

Note:

If you need to remove characters of fixed length from a field, you can use RIGHT(Field,LEN(Field)-Number of Characters).