Overview
Version
| FineBI Version | Functional Change |
|---|---|
6.0 | / |
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.
Example
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:
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.
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.
