Check the string character

[SH_94]

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.

[suwenchuan]

Hi there, a possible solution attached

[BS_THE_ANALYST]

@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.

[BS_THE_ANALYST · Accepted solution]

@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. 

[SH_94]

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.

[binuacs]

@SH_94 Another method