Skip to main content

In my example, the fields “Name” and “Code” should start with the exact same 10 digits. I’d like to create a formula that compares the first 10 digits of both fields and displays a message if they match. Since the content of the “Name” field gets entered independently from the “Code” field, I’d like to use this formula as a way to check for errors.



I started with the following formula:


IF(

AND(

REGEX_MATCH(Name,’\w{10}\b’),

REGEX_MATCH(Code,’\w{10}\b’)

),

‘Codes Match!’,’’)


I am realizing now, this formula only looks for 10 digits, regardless of content. Not good.

How do I specify that the first 10 digits of both fields have to be identical to display a positive result?


Is using LEFT(Name,10) and LEFT(Code,10) the right way to go? If yes, how would I work them into a formula? I can’t seem to figure that out.


Thank you in advance!

Either REGEX_MATCH or LEFT would work.


Right now you’re just checking if there is a value for both {Code} and {Name}. With either REGEX or LEFT, you’d add a third condition to your AND() to check if they’re identical.


IF(

   AND(

      {Name}, 

      {Code}, 

      LEFT({Name}, 10) = LEFT({Code}, 10)

   ), 

   "Codes Match!", 

   ""

)

Either REGEX_MATCH or LEFT would work.


Right now you’re just checking if there is a value for both {Code} and {Name}. With either REGEX or LEFT, you’d add a third condition to your AND() to check if they’re identical.


IF(

   AND(

      {Name}, 

      {Code}, 

      LEFT({Name}, 10) = LEFT({Code}, 10)

   ), 

   "Codes Match!", 

   ""

)

Thank you so much, @Kamille_Parks !

Either REGEX_MATCH or LEFT would work.


Right now you’re just checking if there is a value for both {Code} and {Name}. With either REGEX or LEFT, you’d add a third condition to your AND() to check if they’re identical.


IF(

   AND(

      {Name}, 

      {Code}, 

      LEFT({Name}, 10) = LEFT({Code}, 10)

   ), 

   "Codes Match!", 

   ""

)

@Kamille_Parks - one more question: in my “real world” base, the field {Code} is a lookup field.

When I try your formula pointing to that lookup field, I get an #ERROR!.

Is there a way to turn the lookup field into a text string?

@Kamille_Parks - one more question: in my “real world” base, the field {Code} is a lookup field.

When I try your formula pointing to that lookup field, I get an #ERROR!.

Is there a way to turn the lookup field into a text string?


I just found the answer - if {Code} is a Lookup field, the formula needs to say:


IF(

AND(

{Name},

{Code},

LEFT({Name}, 10) = LEFT(ARRAYJOIN({Code}), 10)

),

“Codes Match!”,

“”

)

Reply


Terms & Conditions