Hi Community,
I have few examples below and i want to check which one meeting the criteria:
Criteria: the length should be 5 and consist of number only. Exclude any letter, symbol or decimal or other character
Example
29604
90.33
56_96
78N01
3920
91738
Output Result
29604
91738
May i know how can i build the formula to cater the requirement above?
Thank you.
Solved! Go to Solution.
@suwenchuan I think your solution could fall flat if you had something like this: 123.45 or 123_45
After removing the decimal, the length would be 5. However, this doesn't match the crieria? Tricky one.
Although 123.45 is a number @SH_94? Can you clarify.
@SH_94 I'd suggest using a regex match:
This says, if the cell contains EXACTLY five digits, it will output as True. Everything else, 6 digits, 4 digits. text etc will all be false.
Hi @BS_THE_ANALYST ,
Thank you for the question and prompt response as well.
From the data, i notice that your example exist as well. Hence, the most appropriate and first check is to determine whether the length is 5 before doing the second check. Once the first check is pass, we check if string contain number only.
Thanks.
@SH_94 Another method