Intercept the specified characters in a field

1.1 Problem description

The user needs to get the content of a segment in a field.

For example, to intercept the last few fixed digits of a string, the effect is shown in the following figure.

1.2 Implementation idea

Use the text function to intercept characters.

2.1 Operation steps

Take the RIGHT function as an example, intercept the last eight digits of the "phone" number to build a new field "left function".

1）Use the demo data "Supplier Information table" to create a self-service dataset, and select the fields "Supplier ID" and "Phone".

2）Select the new column, using the RIGHT function, the new column "left" function intercepts the last eight digits of the phone number, as shown in the following figure.

Function description.

2.2 Effect view

The effect of intercepting the last few fixed digits of a string is shown in the following figure.

Use function: LEFT function and FIND function (FIND function can find the position of a character in the string)

Example data: interceptedfields.xlsx

3.1 Operation steps

For example, from the "user account and password" field, extract the user account to build a new field "left Interceptd not fixed".

1）Make a self-service dataset using the downloaded interceptedfields.xlsx and select all fields.

2）Add a new column and enter the formula LEFT(user account and password,FIND("：",user account and password)), as follows.

Note: If you do not need the ":" in the field, you can change the formula to LEFT(user account and password, FIND(":",user account and password)-1).

Formula description.

3.2 Effect view

Get ":" and all the characters before it from the string, as shown below.

Functions used: RIGHT、FIND 、LEN。

Example data: Section 3 data

4.1 Operation steps

Intercept the password in the "user account and password" field to form a new "Password" column.

Add a new column, enter the formula: RIGHT(user account and password,LEN(user account and password)-FIND("：",user account and password))as shown below.

Formula description.

4.2 Effect view

Intercept all the characters after ":" as shown below.

Sample Data: Intercept Fields-Variable-Length.xlsx

Get the password in the middle of "user account and password" to form a new column "mid password".

5.1 Operation steps

1）Make a self-service dataset using the "Intercepted Fields - Variable-Length" table.

2）Add a new column and 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), as shown below.

Formula description.

5.2 Effect view

Get all the characters after ":" and before ",". The following figure shows.

Example data: text-intercept.xlsx

6.1 Operation steps

Upload data and use the uploaded Excel data set to create a self-service data set, select all fields, add "New Column", enter the formula: LEFT(Province,LEN(Province)-6), click "OK", as shown in the following figure.

Formula description.

6.2 Effect view

Remove the "Branch" suffix at the end of the field and keep the company name, as shown below.

Note: If you need to remove the specified number of strings before the field can be used RIGHT (field, LEN (field) - the number of strings).