Checking for a string of cahracters

[Anonymous]

I need to find all the records that have columnA like "C12345". The value must begins with the letter "C" followed by 5 numbers. 

for example "C12345" is true but "C1234Y5" is false.

Thanks

[rsbin · Accepted solution]

@Anonymous ,

Please try this as a Calculated Column:

Check = SWITCH(
            TRUE(),
            AND(( LEFT( [ColumnA], 1 ) = "C"), ISERROR( VALUE(MID( [ColumnA],2,5 ))) = FALSE() ), "True",
            "False" )

Regards,

[collinsg · Accepted solution]

Hello @Anonymous ,

Here's a suggestion using Power Query to add a custom true/false column, true for C12345 columns and false otherwise. You can then filter on the column. You may need an extra check like Text.Length(txt) = 6 if you wish true to be only when the string is 6 characters long.

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjY0MjYxVYrVgTIjIexkhLALugql2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnA = _t]),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"ColumnA", type text}}),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "Valid",
    each
    let
      txt = Text.Start([ColumnA], 6),
      firstOK = Text.Start(txt, 1) = "C",
      secondPart = Text.End(txt, 5),
      secondOK = try Value.Type(Value.FromText(secondPart)) = Number.Type otherwise false,
      result = firstOK and secondOK
    in
      result,
    type logical
  )
in
  #"Added Custom"

 Hope this helps - example output.

[rsbin · Accepted solution]

@Anonymous ,

Please try this as a Calculated Column:

Check = SWITCH(
            TRUE(),
            AND(( LEFT( [ColumnA], 1 ) = "C"), ISERROR( VALUE(MID( [ColumnA],2,5 ))) = FALSE() ), "True",
            "False" )

Regards,