I would like to think of a way to determine only numeric type records in a string type field.
This is a sample data.
Suppose that you want to determine the numeric or not at the each record. For example, #4 row “apple” is completely text, but others are numeric. It seems possible to do this using the Is function, but it is not easy.
Anti-pattern
You might find the IsNumer function in the function list. And then you can try as follows.
IsNumber(ToNumber([String]))The result is as follows.
It means that the combination IsNumber and ToNumber don’t work. Because the result of the ToNumber function is numeric type, IsNumber function returns “True” whether the value is numeric or not. IsNumber function only check the field type.
How to determine the numeric value or not
There are 3 ways.
1. Use ToNumber function
IsNumber function only determines the field type, so you can’t use it for this purpose. So I introduce the combination IsNull and ToNumber function way. When you try to change the text value to numeric by ToNumber function, the value will be Null. In other words, you can use ToNumber function and then if the IsNull function’s result is False, you can determine that as numeric type. The specific way is as follows.
!IsNull(ToNumber([String]))The result is as follows.
This result is not what we expect. Why? The reason is that when the ToNumber function try to change the string value, it returns 0. 0 is numeric. But never give up! ToNumber function has some options.
As described above, there are 3 options bIgnoreErrors, keepNulls and decimalSeparetor. But when you select that function on fx button, those options will not be inserted. If you want to use those options, you type them directly.
Those options are as follows.
- bIgnoreErrors
If you turn on this option, ToNumber function ignores the errors. If you don’t like warning, you should to turn on this option.
- keepNulls
By default, when the ToNumer function can’t change into the numeric value, it returns 0, But you turn on this option, it returns null. So that, this option will work well for what we want.
- decimalSeparator
You can specify the thousands separator. If you specify this, the symbol will be recognized as a delimiter and convert to a numver correctly.
In other hands, you can use this functions as below for what we want.
!IsNull(ToNumber([String],"True","True",","))If you use that functions, it outputs as follows.
2. Use Select tool and IsNull function
The way 1 is using the ToNumber and IsNull function. But this way2 is changing from using ToNumber function to using Select tool.
The way is that you use the Select tool for changing the numeric type from string type. If the result is null, it is not numeric type. So, you can use the IsNull function to determine the data type “numeric” or other type. But there is thousands separator, it will be determined as string, so replace the separator to empty in advance by Replace function. The workflow is as follows.
- You can use IsEmpty or IsNull function in the Formula tool.
The 1st Formula tool :
Select tool :
The last Formula tool :
The result as follows.
use the regular expression
You can also use RegEx for this purpose. For example, I made a regular expression as follows.
^[+-]*[d,]+.*d*$これは、以下のような条件で判定しています。
- Whether there are numbers from the beginning of the line to the end of the line
- Even if there is a + or – sign at the beginning of the row, it is OK.
- Decimal number is OK.
- Even if with or without numbers after the decimal point, it is OK.
- Even if there is thousands separator, it is OK. But it is not strictly checked. In other words, the thousands seperator position is not checked.
But there is only “+,.”, it would return “True”. If you’re in trouble in that case, you can add the additional condition.
When you use the RegEx tool, the setting is as follows.
You can use the simple regular expressioon, but you are not used to use the regular expression, it would be difficult. If so, you should use the pattern 1 or 2.
The affect of the locale
By the way, there are some countries where the decimal character is not a period, but a comma. In those country, the thousands separator is a period, not comma. So, you need to adjust this in countries. When you handle the world wide data, you need to check them and fix it.
Conclusion
- I explained how to check the each record is numeric or string in string type field.
- The ways are the combination of the IsNull and ToNumber function, the Select tool and IsNull/IsEmpty function, or RegEx tool.
- Also pay attention to the locale (periods and commas are swapped in French or some contries)
Sample Workflow download
Next blog post
I would like to explain functions for a while.
コメント