OmniDocExtract or Remove Characters From Text in Excel
Learn the Excel formula to remove characters from the left of a cell using RIGHT and LEN, plus LEFT, MID and FIND for removing from other positions.
Codes, IDs and labels often arrive with a prefix you don't need, such as "INV-" in front of an invoice number or a region code before a customer name. Removing those characters by hand is slow and error-prone once you have more than a few dozen rows. This guide shows the Excel formula to remove characters from left in a cell, and the related formulas for removing characters from the right or from the middle.
The core method uses two text functions. RIGHT returns the characters at the end of a cell, and LEN returns the total number of characters. Combined, they keep everything after the part you want to drop. You don't need to combine text or join cells to do this, so the formulas stay short.
By the end, you'll be able to strip a fixed number of characters from the start of a cell, strip a variable amount up to a separator, and check the results before you replace the original data.
How do you use an Excel formula to remove characters from the left? The short answer
Use RIGHT with LEN, subtracting the number of characters you want to remove. For example, =RIGHT(A2, LEN(A2)-4) removes the first four characters of cell A2.
| Your situation | What to do | Why |
|---|---|---|
| You always remove the same number of characters | Use =RIGHT(A2, LEN(A2)-n), with n set to that number | RIGHT keeps the end of the text, and LEN tells it how much to keep |
| The text has a separator, such as a space or dash | Use =RIGHT(A2, LEN(A2)-FIND(" ", A2)) | FIND locates the separator, so the length adjusts for each cell |
| You need to remove characters from the end instead | Use =LEFT(A2, LEN(A2)-n) | LEFT keeps the start of the text and drops the last n characters |
| You want the characters you removed, not the rest | Use =LEFT(A2, n) or =MID(A2, start, count) | These extract a piece of text rather than stripping it |
Before you start
Check these points first. Each one prevents a common cause of wrong or blank results.
- Look for extra spaces. A cell that reads "INV-0042 " with a trailing space has one more character than it appears to. Wrap the reference in TRIM, such as
TRIM(A2), if spacing is inconsistent. - Confirm the number of characters to remove. Count them in one sample cell, or use
LENon the prefix alone. A miscount shifts every result by one character. - Check that numbers are text, or handle them as text. Formulas treat values as text. A number like 00123 stored as a number loses its leading zeros, so format the column as text before you start if those zeros matter.
- Keep a copy of the original column. Formulas return new values in another column. Keep the source data intact until you've checked the output, because deleting it breaks the formulas.
- Decide whether the data is typed or in a PDF. If the values sit inside a PDF table, export that table to Excel first using the PDF editor in the OmniDoc desktop client, then apply the formulas to the exported sheet.
Removing characters from the left, step by step
- Put a sample value in a cell. Use a cell that shows the pattern clearly, such as INV-2024-0042 in cell A2. Keeping one example in view makes the formula easy to verify.
- Count the characters you want to remove. For the prefix "INV-", the count is 4, including the dash. Count the characters you want gone, not the ones you want to keep.
- Enter the formula in a new cell. Type
=RIGHT(A2, LEN(A2)-4)in B2. LEN(A2) returns 13 for the sample, so the formula subtracts 4 and asks RIGHT for the last 9 characters. - Confirm the result. B2 should show 2024-0042. If it shows something shorter or longer, recount the prefix in step 2.
- Handle short cells. If a cell holds fewer characters than the number you're removing, LEN minus n becomes negative and the formula returns an error. Use
=IF(LEN(A2)>4, RIGHT(A2, LEN(A2)-4), "")to return a blank instead. - Fill the formula down. Drag the fill handle or copy B2 down the column to match the rest of your data. Relative references like A2 adjust for each row automatically.
Other ways to strip characters
Use these variations when the characters you need to drop aren't a fixed count from the start.
- Remove everything up to a separator. Use
=RIGHT(A2, LEN(A2)-FIND("-", A2))to drop a prefix that ends with a dash. FIND returns the position of the first dash, so the formula keeps everything after it. A name like "Jane Smith, Sales" withFIND(" ", A2)returns "Smith, Sales". - Remove characters from the end. Use
=LEFT(A2, LEN(A2)-2)to drop the last two characters of a code such as A1234-XX, which leaves "A1234-". - Extract a middle section. Use
=MID(A2, 4, 4)to pull four characters starting at position 4. For "ID-2024-0042", this returns "2024". MID takes a start position and a count, so it works when the text you need sits in the middle.
Check the result
Review a sample of rows before you use the output or delete the source column.
| Check | How to see it | If it's wrong |
|---|---|---|
| Characters removed correctly | Compare the first few rows against the source | Recount the characters to remove and update the number in the formula |
| Trailing or leading spaces | Look for results that seem offset by one character | Wrap the reference in TRIM and recalculate |
| Errors in the column | Scan for #VALUE! or other error values | Add the IF check from step 5 to return a blank for short cells |
| Blanks where text should appear | Filter for empty results | Check whether those cells are shorter than the number you're removing |
| Results that won't stay put | Delete or edit the source and see if the result changes | Copy the column and paste as values so the formula is no longer needed |
If you're checking translated or machine-generated content, verify important details against the original source as well. Formulas only change the text they're applied to, but errors in the source carry through.
❓ FAQ
1. Why does my formula return #VALUE!?
This usually happens when the number of characters to remove is larger than the length of the cell. LEN minus n becomes negative, and RIGHT can't handle a negative count. The IF version in step 5 avoids the error.
2. Can I remove characters from the left without a formula?
Yes. Many spreadsheet programs include a fill feature that detects a pattern from a few examples you type, and it can fill the rest of the column. It works well when the pattern is consistent. For a fixed count or a separator, the formula is more predictable and easier to audit.
3. Will the formula change my original cells?
No. A formula in column B reads from column A and doesn't alter it. The original values only change if you edit them directly. If you want the results to stand alone, copy them and paste as values.
4. Can I combine this with other text cleanup?
Yes. Once the unwanted characters are stripped, you can apply TRIM to tidy spacing or other text functions to reformat what's left. Keep each step in its own column so you can check the result at each stage.
📝 Summary
The Excel formula to remove characters from left uses RIGHT with LEN, subtracting the number of characters you want to drop. For separators, use FIND to set the start point, and use LEFT or MID when you need to remove from the end or extract a middle section. Test on a sample row, handle short cells with IF, and paste as values before you delete the source.
