Excel Formula for the First 3 Digits: Text or Numbers?

Formulas & functions · Updated November 18, 2026

To return the first three characters from a value in cell A2, use =LEFT(A2,3). The result is text, even when the characters look like a number; if you need a numeric result instead, use =VALUE(LEFT(A2,3)).

The quick answer: use LEFT

In Microsoft Excel for Microsoft 365 on Windows, the LEFT function returns characters from the beginning of a text string. Enter the formula in another cell, such as B2, and replace A2 with the cell that contains your value. For example, if A2 contains 847291, the result is 847. If A2 contains the text INV-847, the result is INV.

The distinction matters: Excel reads “first three digits” in two different ways. Some people mean the first three characters, which may include letters, spaces, or punctuation. Others mean the first three numeric digits found anywhere in the value. The simple formula handles the first meaning. If your data includes separators or letters before the digits, see the section on extracting digits only.

When the source has fewer than three characters, LEFT returns whatever is available rather than adding characters. A2 containing 56 returns 56, and an empty cell returns an empty result. The formula does not require an exact three-character input.

How the formula works

The syntax is LEFT(text,[num_chars]). The first argument is the value or cell to read; the second is how many characters to return. The square brackets in Excel’s function description indicate an optional argument—they are not typed into the formula. If you leave out the second argument, LEFT returns one character.

With a value in A2 and a formula in B2, =LEFT(A2,3) takes the first three characters starting at the left edge. For the text ABCDE, it returns ABC. For 12345, it returns 123. It does not search for a particular character, skip spaces, or decide which characters are numerically meaningful; it simply counts positions from the start.

After entering the formula, press Enter. To apply it to adjacent rows, select B2 and drag the fill handle—the small square at the lower-right corner of the selected cell—down the column. Excel adjusts the reference to A3, A4, and so on. In a formatted Excel Table, entering the formula in a table column generally fills the formula down that column automatically.

Should the result be text or a number?

LEFT returns text. That is often exactly right for product codes, postal codes, account references, and other identifiers. Text preserves the characters as a label, and you are less likely to accidentally treat an identifier as a quantity. If you need to add, compare, or perform arithmetic with the extracted value, convert it to a number.

With the source value in A2, put this formula in B2 to convert the first three characters to a number: =VALUE(LEFT(A2,3)). If A2 contains 847291, the result is the numeric value 847. The conversion succeeds only when the extracted characters can be interpreted as a number; a prefix such as ABC produces a #VALUE! error. A leading plus or minus sign may also affect how the result is interpreted.

Text and numbers can look identical in a worksheet, so a display of 847 does not tell you which type the cell contains. The difference shows up in later calculations, sorting, or comparisons. For a code that happens to contain digits, keep the result as text. For a number you intend to calculate with, use VALUE and check that every extracted prefix is numeric.

Leading zeros and fixed-width codes

Leading zeros are a common reason to keep extracted digits as text. If A2 contains the text 004817, =LEFT(A2,3) returns the text 004. If you convert that result with VALUE, Excel returns the number 4; numeric values do not retain insignificant leading zeros. That can make a valid code appear to change.

There is an important limit: if Excel already stored an entry as the number 4817, it has no record that the original entry might have been 004817. A number format can display leading zeros, but it does not restore missing information to the underlying value. For identification numbers, import or enter the source column as text when possible, especially when leading zeros are meaningful.

If every source number is supposed to have a known fixed width, you can create a padded text representation before taking its first characters. For example, with a six-digit code stored as a number in A2, use =LEFT(TEXT(A2,"000000"),3) in B2. For the value 4817, the padded representation is 004817 and the formula returns 004. This approach assumes the codes are six digits long; choose a format with the correct width for your data. Padding is not a substitute for knowing the original code length.

When you mean the first three digits, not characters

Suppose A2 contains AB-12-345. The basic formula returns AB-, because those are the first three characters. If you mean the first three digit characters regardless of letters and punctuation, a different formula is needed. In Microsoft 365 or Excel 2021, with a nonblank source value in A2, enter this in B2: =LEFT(TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,"")),3). It returns the text 123 for AB-12-345 by keeping characters that Excel can convert to numbers, joining them, and taking the first three.

This digit-only formula is more complex and should be used only when that is genuinely the rule you want. It removes all nondigit characters, including decimal points and minus signs. For example, it would not preserve a negative sign as part of a number, and it treats digits in a date or reference as a continuous sequence. If punctuation carries meaning, first decide how it should be handled rather than stripping it automatically.

The formula returns text, which is helpful if the extracted digits may begin with zero. It also assumes the source is not blank, because the formula creates a sequence of character positions from the source length. If the data contains letters with accents, symbols, or number formats that require special treatment, test representative rows and confirm the result matches your definition of a digit.

Apply the formula to a column

For a simple worksheet, put the source data in column A, add a heading such as First 3 in B1, and enter the formula in B2. Check a few results before filling down, especially if the column mixes numbers, text, and blanks.

  1. Select B2 and type =LEFT(A2,3).
  2. Press Enter and compare the result with the value in A2.
  3. Use the fill handle to copy the formula down, or copy B2 and paste it into the remaining cells in column B.
  4. If the result will be used elsewhere, decide whether the destination needs text codes or numeric values before converting or copying results.

In Microsoft Excel for Microsoft 365 on Windows, Ctrl+C copies the selected cell and Ctrl+V pastes it. On Mac, use Command+C and Command+V. These shortcuts copy the formula as well as the displayed result. If you need a static output rather than formulas that recalculate, use Paste Special and choose Values after copying; on Windows, the Paste Special command is available from the Home tab’s Paste menu. Check the pasted values before deleting the source column.

Common problems and how to fix them

  • The formula returns letters or punctuation. That is expected if those characters occur first. LEFT counts characters, not numeric digits. Use the digit-only method only if you want to discard every nondigit.
  • Zeros disappear. The result may have been converted to a number. Keep the result as text, or use a deliberate fixed-width format when the source length is known. Changing a cell’s number format cannot recover zeros that were never stored.
  • The result has fewer than three characters. Check whether the source value itself is shorter than three characters, or whether it contains leading spaces that use up some of the first three positions.
  • The formula shows #VALUE!. This commonly occurs when VALUE is asked to convert letters or other nonnumeric characters. Use LEFT alone for a text prefix, or clean and validate the source data before converting.
  • The formula appears as text in the cell. The cell may have been formatted as Text before you entered the formula, or the entry may start with an apostrophe. Change the cell format to General, then re-enter the formula. Simply changing the format may not recalculate a formula already stored as text.

Leading spaces deserve a specific check. If a cell begins with spaces, those spaces count among the first three characters. You can remove ordinary leading and trailing spaces first, but be aware that trimming also changes the source’s spacing. With the original value in A2, this formula returns the first three characters after TRIM removes extra ordinary spaces: =LEFT(TRIM(A2),3). It will not remove every special whitespace character that can appear in imported data.

Alternatives and choosing the right formula

For a prefix at the very start of a cell, LEFT is usually the clearest choice. If the characters you want begin somewhere else, use MID, which extracts a specified number of characters from a specified position. For example, if A2 contains the text INV-847 and you want the three characters after the hyphen, use =MID(A2,5,3) in B2; the result is 847. This assumes the hyphen is always the fourth character. If the prefix length varies, a fixed position will not reliably find the digits.

Another possibility is to clean the source column first, particularly when the values have inconsistent spaces or separators. Cleaning can make a simple formula more dependable, but do not remove punctuation or letters from codes unless the business meaning allows it. When working with imported data, inspect both the displayed value and the cell’s underlying type; a value that looks like a code may have been interpreted as a number or date during import.

Excel formulas use commas between arguments in the examples here. Some regional settings use semicolons as the list separator, so Excel may require semicolons when you enter a formula. If Excel rejects a correctly structured example, check the regional list separator rather than changing the function’s logic.

Frequently Asked Questions

What is the Excel formula for the first three digits?
For the first three characters in A2, use =LEFT(A2,3). It returns text. If you mean three numeric digit characters after skipping letters and punctuation, use a digit-extraction formula instead.

How do I get the first three digits as a number?
With a numeric prefix in A2, use =VALUE(LEFT(A2,3)). The result is numeric, but the formula returns an error if those first three characters cannot be converted to a number.

How do I keep leading zeros in the result?
Keep the extracted value as text with LEFT. If Excel already stored the original code as a number and dropped its leading zeros, those original zeros cannot be inferred without knowing the intended width.

Does LEFT count spaces and hyphens?
Yes. It counts each character from the left, including spaces, hyphens, and letters. If the source begins with a space, that space is one of the characters returned.

Can I use this formula with a whole column?
Enter the formula beside the first data row and fill it down. In an Excel Table, a formula entered in a table column can fill through that column automatically; confirm that the row references align with your source data.

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.