Common Excel Formula Examples
Explore common text functions using a fixed pipe-tag sample, with each formula and its expected result.
Inputs
Updates as you typeResults use the fixed A1 sample. Use the extract or split tool to work with your own text.
A1 = /P-PD2C-PIPE-BP-033
Results
Expected result
Excel 2016 / 2019 / Microsoft 365
=MID(A1,4,4)Overview
Explore common text functions using a fixed pipe-tag sample, with each formula and its expected result. Use it to compare what each function returns on the same tag before writing your own formula, and to pick the function you need.
Formula
A1 = /P-PD2C-PIPE-BP-033 MID · LEFT · RIGHT · FIND · SEARCH LEN · SUBSTITUTE TEXTBEFORE · TEXTAFTER (Microsoft 365)
Example
How to use
- Put /P-PD2C-PIPE-BP-033 in A1 exactly as shown. This page uses a fixed sample.
- Select one of nine functions and compare its formula and preview. MID returns PD2C, RIGHT returns 033 and LEN returns 19.
- Paste the formula into another cell, then vary source length or case to compare behavior. Use the extract or split helper to generate formulas for your own text.
Engineering notes
- FIND is case-sensitive. SEARCH is case-insensitive and supports wildcards.
- FIND/SEARCH return #VALUE! in Excel when no match exists. Add IFERROR when your worksheet requires a fallback.
- The preview is a fixed example, not a live workbook calculation. TEXTBEFORE/TEXTAFTER are available in Microsoft 365 and Excel 2024, not Excel 2016/2019.
- RIGHT returns 033 as text. Converting it to a number removes the leading zero. Check the A1 reference and your regional argument separator.
- Invisible leading or trailing spaces shift FIND and LEN positions by one. If a result looks wrong, check the length with =LEN(A1) first and remove spaces with TRIM.
Frequently asked questions FAQ
Does this read or modify my workbook?
No. It displays sample formulas in your browser and does not upload or change any Excel file.
Why do FIND and SEARCH show the same result?
FIND("PD2C",A1) and SEARCH("pd2c",A1) both return 4 in this sample. FIND("pd2c",A1) instead returns #VALUE! because its case differs. SEARCH ignores case, so equal results do not make the functions interchangeable.
How do I show a blank instead of FIND's #VALUE!?
Wrap it in IFERROR, as in =IFERROR(FIND("X",A1),""). Hiding errors can also hide data problems, though, so it is better to see them while reviewing.
Is there a way to count the delimiters?
=LEN(A1)-LEN(SUBSTITUTE(A1,"-","")) subtracts the length without hyphens from the original length. The sample has 4, so it has 4 + 1 = 5 segments.
Related tools
Excel Text Extract Helper
Suggest fixed-position or delimiter-based Excel formulas from source text and the substring you want to extract.
Open calculatorString Split Helper
Preview text split by a literal delimiter, with TEXTSPLIT for Microsoft 365 and guidance for older Excel versions.
Open calculatorUnit Conversion Formula Helper
Copy arithmetic or Excel CONVERT formulas that convert the value in cell A1 to another engineering unit.
Open calculator