LOWER

Veldhuis Installatie BV gave you its receivables aging list. The invoice numbers were typed by different people: VF-25-0412, vf-25-0413, Vf-25-0414.

Your data analysis tool compares invoice numbers character by character, and the general ledger export holds them in lowercase. You add a column with every invoice number in lowercase before you match the two.

Formula in D2

=LOWER(B2)

=LOWER(B2)

Result: vf-25-0412

Watch out: Excel itself already treats VF-25-0412 and vf-25-0412 as equal, so a lookup in Excel would have worked without LOWER. The column only matters because the tool you export to is case-sensitive.

=LOWER(B2:B6)

=LOWER(B2:B6)

You match the sales invoices in the receivables subledger to the revenue postings on journal VRK in the general ledger. The subledger holds invoice numbers such as vf-25-0414.

In the XAF auditfile the number sits in the description with a space instead of a hyphen and in mixed case: Vf 25-0414. You turn the description into the subledger format: spaces become hyphens, letters become lowercase.

Formula in E4

=LOWER(SUBSTITUTE(B4, " ", "-"))

=LOWER(SUBSTITUTE(B4; " "; "-"))

Result: vf-25-0414

Watch out: A description with a space at the start or end gives a key with a hyphen at the start or end. Add TRIM inside SUBSTITUTE if the export is not clean.

=LOWER(SUBSTITUTE(TRIM(B4), " ", "-"))

=LOWER(SUBSTITUTE(TRIM(B4); " "; "-"))

For journal entry testing you look at the manual MEM entries of Veldhuis Installatie BV. You want to know how often the word storno (a reversal) appears in the descriptions, because reversals need explaining. The preparers wrote Storno, storno and STORNO, and one description uses it twice.

COUNTIF cannot count inside text, and SUBSTITUTE, the usual counting trick, is case-sensitive. You lowercase the descriptions first so one SUBSTITUTE catches every spelling.

Formula in E2

=SUMPRODUCT((LEN(B2:B6) - LEN(SUBSTITUTE(LOWER(B2:B6), D2, ""))) / LEN(D2))

=SUMPRODUCT((LEN(B2:B6) - LEN(SUBSTITUTE(LOWER(B2:B6); D2; ""))) / LEN(D2))

Result: 44

Watch out: Type Storno with a capital in D2 and the formula silently returns 0: the descriptions are lowercased, the search word is not, and nothing matches. Wrap D2 in LOWER as well if other people type the search word.

=SUMPRODUCT(--ISNUMBER(SEARCH(D2, B2:B6)))

=SUMPRODUCT(--ISNUMBER(SEARCH(D2; B2:B6)))

=COUNTIF(B2:B6, "*" & D2 & "*")

=COUNTIF(B2:B6; "*" & D2 & "*")

You test purchases for duplicate invoices. The INK journal export holds the supplier relation and a reference field such as Inv: FA 25-0388, typed with different capitals, extra spaces and the number after a prefix. Your data analysis tool compares keys byte for byte.

You build the key in Excel from three parts, all lowercase, so the same invoice booked twice gets the same key:

• supplier code
• invoice number without prefix and spaces
• amount

The formula will be copied down 20,00020.000 rows, so it has to be readable enough to review once and trust everywhere.

Formula in E3

=LET(
  rel, TRIM(A3),
  inv, SUBSTITUTE(TRIM(MID(B3, FIND(":", B3) + 1, LEN(B3))), " ", ""),
  amt, C3,
  LOWER(rel & "|" & inv & "|" & amt)
)

=LET(
  rel; TRIM(A3);
  inv; SUBSTITUTE(TRIM(MID(B3; FIND(":"; B3) + 1; LEN(B3))); " "; "");
  amt; C3;
  LOWER(rel & "|" & inv & "|" & amt)
)

Result: c2002|fa25-0391|12100

Watch out: The mirror image, LOWER inside the pieces and the join on the outside, gives the same result here but breaks the moment someone adds a piece and forgets its LOWER. One LOWER on the outside cannot be forgotten.

=LOWER(TRIM(A3) & "|" & SUBSTITUTE(TRIM(TEXTAFTER(B3, ":")), " ", "") & "|" & C3)

=LOWER(TRIM(A3) & "|" & SUBSTITUTE(TRIM(TEXTAFTER(B3; ":")); " "; "") & "|" & C3)