Advice on using the TRIM() formula to validate a 18-digit code

Platform discussions Special workflows & use cases
1

Hi,

My problem:

Customer enters a unique 18 digit code which is written for example as 935 999 123 345 654 798

Salesperson at our company then enters that code in a monday text column. I want to validate that the number is 18 digits.

What i have done:
used the trim() function to leave one space between each word and then len(). I have then used conditional coloring to highlight the two correct values (18,23).

18 is the length of the string without spacing and 23 with one space between each.

I can now deal with:
A number with for example 20 digits without spacing.
a number with for example 20 digits but too much spacing between each word.

I cannot deal with exceptions where the sales rep has entered for example.
7359991111111 11111

I.e, correct number of digits but with one space.

Ideally i would need a formula to split the text string and join without spaces and then use len() formula. Any tips?

2

@SolarenergyShannon

This should get you started:

IF(LEN(SUBSTITUTE({Code}, " ","")) <> 18,
  "đŸŸ„Wrong Length",
  IF(SUBSTITUTE({Code}, " ", "") <> REGEXEXTRACT(SUBSTITUTE({Code}, " ", ""), "\d+"),
    "đŸŸ„Not All Numbers", 
    IF({Code} <> REGEXREPLACE(SUBSTITUTE({Code}, " ", ""),
      "(\d\d\d)(\d\d\d)(\d\d\d)(\d\d\d)(\d\d\d)(\d\d\d)", 
      "$1 $2 $3 $4 $5 $6"),
        "đŸŸ„Wrong Spacing", "✅All Good!"
    )
  )
)

Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you?
We Create Custom Solutions
Schedule a 1-on-1 Tutorial Session (for monday, Make & The meaning of Life)

3

Thanks so much for the quick reply.

Im assuming {code} is a reference to the column which i have changed.

Im getting an error in the formula. Do you know of a way to debugg the code in monday? Im having trouble finding the issue as it is hard to decifer where is it going wrong.

4

@SolarenergyShannon

Yes, replace {Code} with {Number} or whatever your text column is called. If you give me the name, I’ll repost with same.

Debugging monday formulas is a bit of an art. There are tricks I use. But it can be a challenge.

5

Hi again,

See the .png of the code column name. This is my code which is giving me an error

IF(LEN(SUBSTITUTE({AnlĂ€ggnings-ID}, " “,”")) <> 18,
“:red_square:Wrong Length”,
IF(SUBSTITUTE({AnlĂ€ggnings-ID}, " ", “”) <> REGEXEXTRACT(SUBSTITUTE({AnlĂ€ggnings-ID}, " ", “”), “\d+”),
“:red_square:Not All Numbers”,
IF({AnlĂ€ggnings-ID} <> REGEXREPLACE(SUBSTITUTE({AnlĂ€ggnings-ID}, " ", “”),
“(\d\d\d)(\d\d\d)(\d\d\d)(\d\d\d)(\d\d\d)(\d\d\d)”,
“$1 $2 $3 $4 $5 $6”),
“:red_square:Wrong Spacing”, “:white_check_mark:All Good!”
)
)
)

testanl

6

@SolarenergyShannon

IF(LEN(SUBSTITUTE({AnlÀggnings-ID}, " ","")) <> 18,
  "đŸŸ„Wrong Length",
  IF(SUBSTITUTE({AnlÀggnings-ID}, " ", "") <> REGEXEXTRACT(SUBSTITUTE({AnlÀggnings-ID}, " ", ""), "\d+"),
    "đŸŸ„Not All Numbers", 
    IF({AnlÀggnings-ID} <> REGEXREPLACE(SUBSTITUTE({AnlÀggnings-ID}, " ", ""),
      "(\d\d\d)(\d\d\d)(\d\d\d)(\d\d\d)(\d\d\d)(\d\d\d)", 
      "$1 $2 $3 $4 $5 $6"),
        "đŸŸ„Wrong Spacing", "✅All Good!"
    )
  )
)
7

Thank you. This solved the probem.

1 Like
8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.