EXCEL / Common Excel Formula Examples

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 type

Results 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

PD2C

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

=FIND("PD2C",A1) returns 4. =SEARCH("pd2c",A1) also returns 4, but ignores case. =LEN(A1) returns 19. Combining functions tells you more. =LEFT(A1,FIND("-",A1)-1) returns /P, the part before the first hyphen, and =LEN(A1)-LEN(SUBSTITUTE(A1,"-","")) counts 4 hyphens; the number of segments is hyphens + 1 = 5.

How to use

  1. Put /P-PD2C-PIPE-BP-033 in A1 exactly as shown. This page uses a fixed sample.
  2. Select one of nine functions and compare its formula and preview. MID returns PD2C, RIGHT returns 033 and LEN returns 19.
  3. 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.

KEEP WORKING