Field Cleaning

Version

Sometimes, unnecessary characters need to be cleaned from the data, such as brackets surrounding negative numbers, special currency symbols like ¥, and thousands separators in numeric data, as shown in the following figure.

Implementation Method

Use the LEFT function to extract the first character of each field and compare it with ( to determine whether the number is positive or negative.

Use the REPLACE function to replace unnecessary characters in each field, such as brackets, currency symbols, and thousands separators.

Creating an Analysis Subject

Log in to the FineBI system as admin, choose My Analysis > Add Analysis Subject to create a subject, and click Edit, as shown in the following figure.

Adding Data

Click to download sample data: Profit Data.xlsx

Click , select Local Excel, select the sample data, and click OK, as shown in the following figure.

Adding a Formula Column

1. Select the sample data table to enter the data editing page.

2. Add a formula column.

• Name of the added formula column: New Profit.

• Formula: IF(LEFT(Profit,1)="(","-"+REPLACE(REPLACE(REPLACE(REPLACE(Profit,"￥",""),",",""),"(",""),")",""),REPLACE(REPLACE(Profit,"￥",""),",",""))

• Field type: Value

3. After completing the settings, click Save All and Update, as shown in the following figure.

The formula above involves two functions:

• LEFT - Returning the First or First Several Character(s) of Required Numbers

This function allows the system to return the first or first several character(s) in the text, based on the number of characters you specify.

You can directly use this function to extract the first character of each field and compare it with ( to determine whether the number is positive or negative.

• REPLACE - Replacing Part of Text

This function allows the specified character string in the original text to be replaced with a different character string.

You can directly use this function to replace unnecessary characters in each field, such as brackets, currency symbols, and thousands separators.

Demonstration

Click OK. In the added column, brackets are converted to negative signs (-), and currency symbols (¥) and thousands separators are removed, as shown in the following figure.