Practical formula helpers / 4 TOOLS

Excel

Extract and split tag strings, convert units and explore common Excel functions.

What this section covers

Build the formulas needed to handle patterned strings such as pipe tags, drawing numbers and material codes in Excel, for example extracting the area code from a tag like /P-PD2C-PIPE-BP-033 or splitting it on a delimiter, matched to your Excel version.

Microsoft 365's TEXTSPLIT, TEXTBEFORE and TEXTAFTER are not available in Excel 2016 or 2019, so older versions get MID, FIND, LEFT and RIGHT combinations or the Text to Columns feature instead.

Which tool should I use?

Common mistakes

  • Confusing FIND and SEARCH: FIND is case-sensitive and SEARCH is not.
  • Using Microsoft 365-only functions in a file opened in Excel 2019: other users see #NAME? errors.
  • Losing the leading zero of number-like text such as 033: keep the result as text or format it with TEXT.