Excel Formula to Extract the First Five Digits from D2

Formulas & functions · Updated November 18, 2026

Assuming D2 contains the text or number you want to search, use =IFERROR(IF(D2="","",LEFT(TEXTJOIN("",TRUE,IFERROR(MID(D2,SEQUENCE(LEN(D2)),1)*1,"")),5)),"") to collect the first five digits found anywhere in the cell. The result is text, so leading zeros in the extracted digits are retained.

The Formula and What It Returns

This formula scans D2 from left to right, keeps characters Excel can convert to numbers from 0 through 9, joins those digits together, and returns the first five. It does not require the digits to be next to one another. For example, if D2 contains Ref A-03/8472, the formula returns 03847.

The formula assumes D2 contains an ordinary text value or number, and that you want digits from anywhere in the cell. It is designed for Microsoft 365 or Excel 2021 on Windows. The formula also returns a blank if D2 is blank or the calculation encounters an error. If the cell has fewer than five digits, it returns all the digits it finds rather than adding extra zeros.

To enter it, select the cell where you want the answer, such as E2, paste the formula into the formula bar or cell, and press Enter. In Microsoft 365 and Excel 2021, SEQUENCE creates the positions for the characters to examine, so you do not need to confirm the formula with Ctrl+Shift+Enter.

How the Formula Works

Each function has a separate job. Understanding the sequence makes it easier to adjust the formula or diagnose an unexpected result:

  • LEN(D2) counts the characters in D2.
  • SEQUENCE(LEN(D2)) generates a list of positions, from 1 through the character count.
  • MID(D2,SEQUENCE(LEN(D2)),1) returns one character from each position.
  • Multiplying each character by 1 converts digit characters to numbers. Letters, spaces, punctuation, and other characters cause conversion errors.
  • IFERROR(...,"") replaces those conversion errors with empty text, effectively skipping non-digits.
  • TEXTJOIN("",TRUE,...) joins the remaining digits without a separator and ignores empty results.
  • LEFT(...,5) takes the first five digits from the joined string.
  • The outside IFERROR and blank check make the result blank for an empty D2 or a calculation error.

The important detail is that the formula first gathers digits from the whole cell and only then takes five. It does not simply read the first five characters. That distinction matters when the cell begins with a label, punctuation, or a shorter number.

Worked Examples

With the source value in D2, the formula produces the following results. These examples show that it ignores non-digit characters and keeps the original order:

  • D2 contains Order 731-4098; the result is 73140.
  • D2 contains AB007-1289; the result is 00712. The leading zeros are preserved because the output is text.
  • D2 contains Room 8, desk 24; the result is 824, since there are only three digits.
  • D2 contains No digits here; the result is blank.
  • D2 is empty; the result is blank.

Notice that the third example combines digits from separate parts of the text. If the intended answer should come only from one uninterrupted five-digit code, this formula may not match the requirement. For example, it turns 12-34567 into 12345, not 34567. Decide whether you want the first five digits in the whole cell or the first five-digit run before choosing a formula.

First Five Digits Versus First Five Characters

Sometimes “first 5 digit” means the first five characters, not five numeric characters scattered through the cell. If D2 starts with the value you want and you simply need its first five characters, a shorter formula is appropriate.

With the full text or number in D2 and a result wanted in another cell, use =LEFT(D2,5). For example, if D2 contains AB123456, this returns AB123, including the letters. If D2 contains 123456, it returns 12345. LEFT does not search past letters or punctuation to gather digits; it just takes characters from the start.

If D2 contains Ref 123456, LEFT returns Ref 1, while the longer formula returns 12345. This side-by-side difference is a useful way to clarify the request before applying a formula down a large list.

Use the Formula Down a Column

For a list of records, put the formula in E2 while the corresponding source value is in D2. Then copy it down so each row checks its own D-cell. You can drag the fill handle, the small square at the lower-right corner of the selected cell, or copy E2 and paste it into the remaining cells in column E. Excel adjusts the relative reference automatically: the next row checks D3, then D4, and so on.

For a structured Excel table, enter the formula in the first row of the output column. Excel generally fills a calculated-column formula through the table and applies it to new rows. Check the first few results after filling: an unexpected value may indicate that the source text is inconsistent or that the intended rule is different.

Keep the extracted result in a separate column rather than replacing D2. This preserves the original value for checking and makes it possible to revise the extraction rule later. If you need fixed results instead of formulas, copy the output cells and use Paste Special > Values to replace formulas with their current results.

Leading Zeros, Numbers, and Cell Formatting

Codes such as postal codes, product IDs, and account references often begin with zero. The formula joins the selected digits as text, so an extracted result such as 00712 remains visibly 00712. That is often preferable to a numeric result, since ordinary numeric values do not retain leading zeros.

There is an important limit: Excel may already have removed zeros when the original value was entered as a number. If someone types 00123 into a General-format cell, Excel can store it as the number 123. A formula cannot recover zeros that are no longer part of the stored value. To preserve identifiers when importing or entering data, store them as text or apply an appropriate import setting before Excel interprets them as numbers.

Formatting also affects what you see, but this formula works from the cell’s stored value rather than attempting to read its visual appearance. A date, for example, is stored as a serial number. If D2 contains a date formatted to display as a date, the formula may collect digits from the underlying serial value rather than from the date as displayed. For identifiers or formatted values, confirm that the underlying cell content is the text you intend to inspect.

Compatibility and Common Problems

The formula uses SEQUENCE, which is available in Microsoft 365 and Excel 2021. It is not suitable as written for older Excel releases that do not support that function. TEXTJOIN is also required. If Excel displays #NAME?, check the Excel version and confirm that the function names are recognized in that installation.

Excel formulas in some regional settings use semicolons rather than commas between arguments. The formula shown here uses commas, as in standard English-language Excel syntax. If Excel rejects the pasted formula and your installation expects semicolons, replace the argument-separating commas with semicolons; do not change commas that are part of the cell’s actual text.

Other common causes of surprising results include:

  • Only a few digits appear: the cell may contain fewer than five digits, or its first digits may not be stored as text or numeric content.
  • The wrong digits appear: the formula collects digits across the entire cell, including numbers in labels, dates, or other text before the code.
  • A displayed date gives an unexpected result: Excel calculations use the stored date serial, not necessarily the characters shown by the date format.
  • The result loses leading zeros after another calculation: a later step may convert the text result to a number. Keep it as text when zeros are meaningful.
  • The formula returns blank: D2 may contain no 0–9 digits, may be empty, or may contain an error that the outer IFERROR is suppressing.

For troubleshooting, inspect D2 directly and compare the intended digits with the actual stored value. If you would rather see an error than a blank when the source contains an error, remove the outer IFERROR wrapper, while keeping the inner IFERROR that skips non-digit characters.

When to Choose Another Method

Use the formula above when the requirement is specifically “the first five numeric digits appearing anywhere in this cell.” Use LEFT when the requirement is “the first five characters from the start.” Use a different approach when the five digits must be consecutive, when a cell contains several possible codes, or when the text has a reliable delimiter that identifies exactly which part to extract.

For a one-off cleanup or a complicated, repeatable import, Power Query can be a better fit than a long worksheet formula. It lets you transform a data column as part of a refreshable process, but the exact steps depend on how the source text is structured. If the data is inconsistent, first define what counts as a valid code and how to handle multiple matches; otherwise, an automated formula can return a technically consistent but unintended answer.

For a large dataset, test representative cases before filling the formula down thousands of rows. Include values with leading zeros, no digits, fewer than five digits, several separated digit groups, and any special formats used in the source. This small check helps confirm that the formula’s “first five digits anywhere” rule matches the real data requirement.

Frequently Asked Questions

How do I extract the first five digits from D2 in Excel?
Use the formula at the start of this page. It scans D2, joins all digit characters in their original order, and returns the first five. It requires Microsoft 365 or Excel 2021 because it uses SEQUENCE.

Does the formula keep leading zeros?
Yes, when the zeros are present in D2 as text or as part of its stored content. The result is text, so extracted zeros remain visible. If Excel already converted an entry such as 00123 to the number 123, the missing zeros cannot be reconstructed from that cell alone.

What if D2 has fewer than five digits?
The formula returns every digit it finds. For example, a cell containing Room 8 returns 8; it does not pad the result with zeros.

How can I get the first five characters instead?
With the value in D2, use =LEFT(D2,5). This returns characters from the start regardless of whether they are digits, letters, spaces, or punctuation.

Does this return five consecutive digits?
No. It gathers digits across the cell, even when letters or punctuation separate them. If you need the first uninterrupted five-digit sequence, use a method designed to identify a consecutive run and verify how it should behave when there are multiple runs or no match.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.