How to find text comprised of specific number of digits?

[UsePowerBI]

Hello

I have a text column like this:

1234

234E

345A

I want to return the values that are numeric only and have 4 digits only.

I wrote the following but it does not work:

IF(NOT(ISERROR(VALUE([Col1]) && LEN([Col1])=4,"YES")

However it does not return anything.

Any idea?

[amitchandak]

@UsePowerBI , Try like

IF(ISERROR(LEN([Col1])), blank(),if( LEN([Col1])=4,"YES",blank()))

[az38]

Hi @UsePowerBI 

for me it works.

Pay attention to parentheses

Column = IF( NOT(ISERROR(VALUE([Col1]))) && LEN([Col1])=4,"YES")

[UsePowerBI]

Yes, I did the parentheses correctly but it does not work.

Maybe the LEN and VALUE cannot go together with an &&?

It returns all blank!

I also tried a calculated column as vALUE([Col1]) and it returns: Cannot convert value 'G257' of type Text to type Number.

[harshnathani]

Hi @UsePowerBI ,

Works for me too

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[UsePowerBI]

Can you format the Column as text please and try this?

CalcCol = IF(LEN(Table1[Col1])=7,"Yes",IF(LEN(Table1[Col1])=5,"No"))

Thanks

[v-kelly-msft]

Hi  @UsePowerBI ,

Is below what you need?

Here is the dax expression:

Column = IF(NOT(ISERROR(VALUE('Table (3)'[Column1])))&&LEN('Table (3)'[Column1])=7,"Yes",IF(NOT(ISERROR(VALUE('Table (3)'[Column1])))&&LEN('Table (3)'[Column1])=5,"No"))

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[az38 · Accepted solution]

@UsePowerBI 

Col1 is formatted as text

maybe you have some spaces or special characters in Col1 values?

[harshnathani]

Hi @UsePowerBI ,

This will give  a BLANK().

What is the exact Output you need.

This check or Length = 7 , gives yes, else searches for legth = 5 and gives no

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!