To remove a set number of characters from the left of a cell in Excel, enter =REPLACE(A2,1,3,"") in a helper column, changing 3 to the number of characters to remove. To remove everything through a separator such as a hyphen, use =TEXTAFTER(A2,"-") in Microsoft 365; copy the results and paste them as values if you want to replace the original text.
What “left text” means in Excel
People use “delete left text” to describe a few different jobs. You might need to remove the first three characters from every entry, strip a shared prefix such as “ID-”, or keep only the part after a space, hyphen, or other delimiter. The right method depends on whether the number of characters is fixed or the point where the unwanted text ends varies.
Excel formulas return a result in a different cell; they do not erase part of the source cell in place. That is usually safer: the original values remain available while you check the result. Once it is correct, you can copy the result column and use Paste Special > Values to replace the originals. If you only need to change one cell, you can also edit it directly in the formula bar or cell.
For example, removing the first three characters from “ABC12345” should produce “12345.” Removing everything before and including the hyphen in “INV-1042” should produce “1042.” Those are different transformations, so be clear about whether the separator itself should remain.
Remove a fixed number of characters from the left
Use REPLACE when every cell needs the same number of leading characters removed. It substitutes an empty string for a specified number of characters starting at a specified position. The position 1 means the first character on the left.
With the original text in A2 and the unwanted prefix exactly three characters long, enter =REPLACE(A2,1,3,"") in B2. For example, if A2 contains “ABC12345,” the result in B2 is “12345.” Fill the formula down the column to process more rows. To remove a different fixed number, change the 3 to that number.
REPLACE is useful when the prefix length is consistent even if its characters differ. If the first two characters in a list are always unwanted, for instance, it removes those two characters regardless of whether they are “ID,” “#,” or something else. If the unwanted portion varies in length, use a delimiter-based method instead.
You can also use RIGHT and LEN, but REPLACE is often easier to read for this task. With text in A2 and three leading characters to remove, =RIGHT(A2,LEN(A2)-3) returns “12345” when A2 is “ABC12345.” Be cautious with this version: if the number to remove is greater than the text length, the length passed to RIGHT becomes negative and Excel returns an error. REPLACE avoids that particular negative-length problem and returns an empty string when the removal extends beyond the text.
Remove everything before a delimiter
When the unwanted left portion ends at a known character, such as a hyphen or colon, TEXTAFTER is a direct option in Microsoft 365 and Excel 2024. It returns the text after the specified delimiter. This is often more reliable than counting characters when prefixes have different lengths.
With entries in A2 and a hyphen separating the prefix from the part you want, enter =TEXTAFTER(A2,"-") in B2. If A2 contains “INV-1042,” the result is “1042”; the hyphen is not included. Fill the formula down for the other rows. The function uses the first matching hyphen by default, so “A-B-C” returns “B-C.”
If the delimiter might be missing, TEXTAFTER returns the #N/A error by default. You can tell Excel to keep the original cell value in that case. With the same A2 layout, enter =IFERROR(TEXTAFTER(A2,"-"),A2). For “INV-1042” it returns “1042”; for “INV1042” it returns “INV1042.” This is helpful when a column contains inconsistent records, although it can also hide unexpected data problems, so inspect the results.
To keep the delimiter while deleting only what comes before it, use MID and FIND. With the original string in A2 and a hyphen as the separator, =MID(A2,FIND("-",A2),LEN(A2)) returns “-1042” from “INV-1042.” FIND is case-sensitive, which matters for letters used as delimiters; for a punctuation separator such as a hyphen, that distinction is irrelevant. If no separator exists, FIND returns an error.
Choose the method that matches the data
Before applying a formula to a whole column, check several representative values and decide what should happen to unusual rows. A formula that works on a typical code may produce unwanted results for a blank cell, an entry with no delimiter, or a value with more than one delimiter.
- Same number of characters in every row: use REPLACE, such as removing the first three characters.
- Prefix ends at a consistent delimiter: use TEXTAFTER to keep the text after it.
- The delimiter must remain: use MID with FIND, or adjust the formula to include the delimiter.
- Only a few one-off edits: edit the cells directly, after checking that a bulk transformation is not needed.
- Repeated cleanup from imported files: consider Power Query so the transformation can be refreshed on later imports.
Consider whether the entries are text or numbers, too. Removing a prefix from a code should normally leave a text result, because leading zeroes may be meaningful: “ID-0042” should often become “0042,” not the number 42. Excel may interpret a formula result as text or a number depending on the formula and source data, so check that leading zeroes, dates, and other formats remain as intended.
Apply the result without losing the original data
A careful workflow makes it easier to catch mistakes before they affect the source list. Keep a copy of the workbook or preserve the original column until the output has been reviewed. Then use a helper column beside the data:
- Insert a blank column beside the text to clean and label it, for example “Cleaned code.”
- Enter the formula in the first data row, changing the cell reference and character count or delimiter to match your sheet.
- Fill the formula down. In Windows Excel, select the formula cell and double-click the fill handle at its lower-right corner to extend it beside adjacent data. You can also drag the handle.
- Check the first, last, and unusual rows, including entries with short text or missing separators.
- If the cleaned results should replace the originals, copy the result cells, select the original cells, and choose Home > Paste > Paste Special > Values. Confirm the pasted text before deleting the helper column.
Pasting values matters because a copied formula normally continues to refer to its source cell. If you overwrite the source column with formulas that refer to that same column, you can create circular references or lose the input needed to calculate the output. A separate helper column avoids that problem. Save the workbook after verifying the replacement.
Other ways to remove a left prefix
For a short list with a predictable pattern, Flash Fill can infer the transformation from examples. Type the desired cleaned version beside the first source value, then enter another example if needed. In Excel for Microsoft 365 on Windows, select the output column and choose Data > Flash Fill, or use Ctrl+E. Review all the generated values: Flash Fill creates results rather than a formula-driven transformation, and a pattern that is ambiguous may be inferred incorrectly. On Mac or Excel for the web, use the available Data menu command if Flash Fill is present; menu availability and keyboard shortcuts can vary by platform.
For a cleanup that must be repeated on new files, Power Query is a more maintainable alternative. In Excel for Microsoft 365 on Windows, select the data and choose Data > From Table/Range to open the Power Query Editor. Select the relevant column, then choose Transform > Extract > Text After Delimiter and specify the separator. Choose Home > Close & Load to return the transformed data to Excel. The precise ribbon options may vary slightly by update or platform. A query keeps the transformation steps so you can refresh the query when the source data changes, rather than copying formulas each time.
Find and Replace is not generally the best way to remove a variable-length prefix. It can replace a known, identical string, but removing “ID-” will not remove a different prefix such as “INV-” unless you account for each variation. Avoid replacing broad characters such as a hyphen throughout a workbook if hyphens are valid elsewhere.
Common problems and checks
The formula shows as text. The destination cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell format to General, then re-enter the formula. Changing the format alone may not recalculate a formula that was already stored as text.
The result is an error. TEXTAFTER can return #N/A when its delimiter is absent; FIND returns an error if it cannot locate its character. Decide whether a missing delimiter should preserve the original, produce a blank, or be flagged for correction. Do not automatically suppress every error if it might indicate a data-quality issue.
Some rows lose the wrong amount. A fixed-count formula removes the same number of characters from every row. If some prefixes are two characters and others are five, a fixed count will cut into valid text. Use a reliable delimiter or standardize the source first.
Leading zeroes disappear. Excel may display a numeric result without leading zeroes. Codes and identifiers are often best retained as text. Check the actual result and cell formatting, not just the formula. Do not convert identifiers to numbers merely because they contain digits.
The formula changes but the displayed result does not. Excel may be set to manual calculation. On Windows, check Formulas > Calculation Options > Automatic, then recalculate if necessary. Also verify that the formula references the intended source row and that the fill-down operation did not shift an absolute or relative reference unexpectedly.
Frequently Asked Questions
How do I delete the first character in Excel?
With the text in A2, enter =REPLACE(A2,1,1,"") in another cell. For “A123,” the result is “123.” Fill the formula down if you need to process a list.
How do I remove text before a space or hyphen?
Use TEXTAFTER with the character that marks the boundary. With the source in A2, =TEXTAFTER(A2," ") returns the text after the first space, while =TEXTAFTER(A2,"-") returns the text after the first hyphen. These functions are available in Microsoft 365 and Excel 2024.
Can I delete left-side text without a formula?
Yes, you can edit a cell manually or use Flash Fill for a small, recognizable pattern. For a large list, formulas or Power Query make the transformation easier to review and repeat. Formulas do not alter their source cells until you copy and paste the results as values.
How do I remove the prefix but keep the hyphen?
With the original value in A2, use =MID(A2,FIND("-",A2),LEN(A2)). For “INV-1042,” it returns “-1042.” This assumes the hyphen exists; otherwise FIND returns an error.
Does LEFT delete text?
No. LEFT returns a specified number of characters from the start of a text value; it does not modify or delete the original. To remove characters from the left, use REPLACE or return the part after a separator with TEXTAFTER.
Related
- Copy and Paste X Times in Excel: Repeat Cells or Rows
- Convert H:M:S to a Number in Excel
- Contact Number List Template for Excel: Setup Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Filters: How to Sort and Show the Rows You NeedTo filter data in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data…
- Excel GROUPBY With Multiple Criteria: Filter ArraysTo group Excel data while applying multiple row-level criteria, use the filter_array argument in GROUPBY and combine AND…
- Excel VLOOKUP Column Index Number ExplainedIn Excel VLOOKUP, the column index number tells Excel which column to return a value from, counting from…
- Excel ISBLANK Formula Examples and Common MistakesExcel’s ISBLANK formula checks whether a cell is truly empty: enter =ISBLANK(A2) to get TRUE if A2 has…
- Proper Case in Excel: Change Names with the PROPER FunctionTo change names to proper case in Excel, enter =PROPER(A2) in a blank column beside the first name,…
- Excel Date Difference in Years: Formulas That WorkTo find the number of complete years between two dates in Excel, use =DATEDIF(A2,B2,"Y"), with the earlier date…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.