How to segregate cell based on the cell value

[Gsiva3]

Hi Team,

I know you can help me on this.

I have a column named Input with value as AA#23,BB#2A,CC#G3,DD#GG. My desired output should have the cell values whose 3rd character are number. So, my output column contains only AA#23,BB#2A as per the condition.

Input	Output
AA#23	AA#23
BB#2A	BB#2A
CC#G3	-
DD#GG	-

[Luke_C · Accepted solution]

Hi @Gsiva3 

Here's one approach that checks if the character is an integer 

if isinteger(substring([Input],3,1))
then [Input]
else null()
endif

[Gsiva3]

@Luke_C 

Thanks Luke👍

Got it done!!

[Ar13f]

Dear @Luke_C 

can you explain the logic of the formula below?

if isinteger(substring([Input],3,1))
then [Input]
else null()
endif

Rgds,

Arief

[Luke_C]

Hi @Ar13f 

The logic is this:

1. Substring([Input],3,1) returns the 4th character in the string (remember numbering starts at 0). The syntax of the substring formula is: Substring(String,start,length)
2. IsInteger() checks if the value that is being processed can successfully be converted to an integer. This function returns true or false.
3. The if statement is just takes the result of the isinteger and says if it's true then we want to list the entire string, otherwise put null. 

Hope this makes sense.

[Ar13f]

Dear @Luke_C 

Thank you very much for the explanation ..👍