How to segregate cell based on the cell value

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	-

Datasets

Accepted answers

Luke_C

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

New Workflow1.yxmd

All comments

Luke_C

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

New Workflow1.yxmd

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:

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)
IsInteger() checks if the value that is being processed can successfully be converted to an integer. This function returns true or false.
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 ..👍

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335