Sometimes, you need to remove unnecessary characters from the data.
Example: parentheses (enclosing negative numbers), special currency symbols ($), and thousands separators (built-in with numeric values), as shown in the following figure.
First, use the LEFT() function to extract the first character of the field and compare it with the left parenthesis to determine whether a number is positive or negative.
Second, use the REPLACE() function to replace unnecessary characters in the field, including parentheses, currency symbols, and thousands separators.
Finally, use the TODOUBLE() function to convert the text-type data to the numeric type.
You can click to download the demo Excel: Order_Profit.xlsx
If you want to follow the steps described in this document, you can import the demo Excel to the database.
Log in to FineDataLink, enter Data Development, create a scheduled task, and add a Data Transformation node, as shown in the following figure.
Enter the Data Transformation page, and add a DB Table Input operator to import the demo Excel for subsequent data cleaning, as shown in the following image.
Click Data Preview to view the demo Excel, as shown in the following figure.
Enter the field name and input the formula, as shown in the following figure.
TODOUBLE(if(left(#{Profit},1)="(","-"+REPLACE(REPLACE(REPLACE(REPLACE(#{Profit},"$",""),",",""),"(",""),")",""),REPLACE(REPLACE(#{Profit},"$",""),",","")))
The following table describes the formula:
REPLACE(Profit,"￥","")
Replaces the dollar sign ($) in the Profit field with an empty string.
REPLACE(REPLACE(REPLACE(Profit,"￥",""),",",""),
"(","")
Replaces the dollar sign ($) in the Profit field with an empty string and removes the left parenthesis.
REPLACE(REPLACE(REPLACE(REPLACE(Profit,"￥",""),",",""),"(",""),")","")
Replaces the dollar sign ($) in the Profit field with an empty string and removes the left and right parentheses.
REPLACE(REPLACE(Profit,"￥",""),",","")
Replaces the dollar sign ($) in the Profit field with an empty string and removes the thousands separator (,).
if(left(Profit,1)="(","-"+REPLACE(REPLACE(REPLACE(REPLACE(Profit,"$",""),",",""),"(",""),")",""),REPLACE(REPLACE(Profit,"$",""),",",""))
Removes the left parenthesis and the dollar sign ($) if the second character of the Profit field is the left parenthesis, and prepend a hyphen (-) to the processed field.
Replaces the dollar sign ($) in the Profit field with an empty string and removes the thousands separator (,) if the second character of the Profit field is not the left parenthesis.
TODOUBLE()
Converts the result from the text type to the numeric type
After completing the above steps, click Data Preview to view the processed field in the Profit_Correction column, as shown in the following figure.
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy