20 years ago my last article for the Accountants Weekly magazine was published. They spelled my name wrong after getting it right for all the other articles, maybe that’s why I stopped.

The original article is shown below.

These are all still applicable in the current version of Excel.

Here are my current thoughts and some extra tips on these functions.

1. TEXT – this function is great for creating headings using dates. If cell A1 contains 1/1/2021 then the following formula will display Report for January 21.

   ="Report for "&TEXT(A1,"mmmm yy")

2. RIGHT / LEFT – a little known trick, if you only need to extract the first or last character you can omit the number. The default number is 1.

   =LEFT(A1)

   =RIGHT(A1)

3. MID – if you are unsure how many characters to extract (last argument in the function) you can use a large number say 1000 and MID will extract all the characters from the position you specify until the end of the string without generating an error.
4. UPPER / LOWER / PROPER – useful for headings where you need to convert entries to uppercase. In the example from 1 above here is an amended formula that diplays Report for JANUARY 21.

   ="Report for "&UPPER(TEXT(A1,"mmmm yy"))

5. LEN – in the latest version of Excel you can use the LEN function with the SUM to SUM based on code length. Is column A has codes and column B has values this will add up the values for code that have 4 characters.

   =SUM((LEN(A2:A5)=4)*(B2:B5))

   If you have an older version of Excel use

   =SUMPRODUCT((LEN(A2:A5)=4)*(B2:B5))

6. TRIM – I used to think TRIM only removed leading and trailing spaces, but it doesn’t. If there are multiple spaces together in the middle of text it removes duplicated spaces and leaves just one space. See image below for an example.
7. SEARCH – useful to create flexible text extractions. If you need the search to be case sensitive, use the FIND function instead.