Skip to main content
Question

Compare two text fields.

  • August 8, 2017
  • 0 replies
  • 112 views

I am trying to compare two text fields [Field1] and [Field2]. If fields do not match, I want to say ?Mismatch.?

 

The example below is a match; I need to create a formula that will always take lower case ?g? and change it to ?G? then see if [Field1] and [Field2] match.

 

Data in [Field1] is 12345678g

Data in [Field2] is 12345678G
    This topic has been closed for replies.

    No problem,
    The operator for = wants and exact match and is case sensitive.

    But the Contains functions is not so picky and tolerates case differences.

    So try this


    If(
    Length([Field 1]) = Length ([Field 2])
    and
    Contains([Field 1], [Field 2]), "Match", "Mismatch")
    If I wrote the formula like this will it return only mismatches:

    If([Customer - Related Client]=20,
    If(Length([Customer - Enrollment - Meter Number]) = Length([Customer - Meter number]) and Contains([Customer - Enrollment - Meter Number],[Customer - Meter number]),"Meter number does not match enrollment"&"\n")
    )&
    I'm not understanding if you are asking me a question. But your formula should be true if they match, but your words say mismatch.
    • KenFirch
    • Registered
    • August 10, 2017
    Formula could be:

    If(Lower(Trim([Customer - Enrollment - Meter Number] & ""))
       <> Lower(Trim([Customer - Meter number] & "")),
       "Mismatch")

    To display only mismatched records, create a report and set the Filter to where that formula field = Mismatch.  This assumes you have entered both meter ID's.  You may want to check for incomplete data also:

    If Trim([Customer - Enrollment - Meter Number] & "") = ""
       OR Trim([Customer - Meter number] & "") = "",
       "Missing Meter Info",
       If(Lower(Trim([Customer - Enrollment - Meter Number] & ""))
          <> Lower(Trim([Customer - Meter number] & "")),
          "Mismatch"))
    Try this then

    If(
    Length([Field 1]) <> Length ([Field 2])
    or 
    not Contains([Field 1], [Field 2]), "Mismatch")
    Terms & ConditionsAccessibility statement