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.
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.
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.
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.
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.
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.
滑鼠選中內容,快速回饋問題
滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy