Excel remove numbers from right

Learn how to remove characters from the right of the text using various Excel functions. Discover how to remove the last n characters from a text string.

If you have a data cleaning task, in some cases, we have to keep only the specific parts of a text or string.

You can save time using a versatile Excel toolbox with custom functions. This article will explain the most used formula examples and function syntaxes. If you want to dive deeply into Excel Formulas, read our definitive guide.

Generic Formula to Remove Characters from Right

=LEFT(text,LEN(text)-n)

Arguments of the LEFT function:

  • text: the text from which you want to remove the last n characters in excel
  • numbers_of_characters: the numbers of characters we want to remove.

The argument of the LEN function:

  • text: the cell which contains text

Let us examine the formula above! We have some functions to strip characters from the right. But we want to find a solution to make sure everyone clearly understands. So, we’ll apply an inverse formula and try to extract the characters from the left.

What should we do? Calculate the result using the following logic:

=(Count the length of the full string) – (N characters from right to left)

How this formula works

Excel remove numbers from right

First, we extract the given number (n characters) from the left of the text.

=LEN(A2)-2 = 6

Now we know the proper length of the string. It’s time to execute the second part of the formula. We will use the result as a parameter of the LEFT formula. To remove the last n characters, use a combination of a LEFT and LEN functions.

=LEFT(LEN(A2)-2) 
=LEFT(6) = Example

TEXTLEFT function to remove characters from right

The TEXTLEFT function did not exist in Excel by default. Fortunately, we have implemented a huge custom function library into our free Excel add-in.

We’ll show you an example to demonstrate the power of user-defined functions.

Excel remove numbers from right

Type the formula:

=TextLEFT(A3,"@")

So the unnecessary part of the text will be removed. TEXTLEFT is one of the effective functions if you are working with delimiters or wildcards in Excel.

Let’s the next example! We want to extract 855 from cell A5. LEN calculates the total length of the string is in A6. The result is 9.

If you evaluate the

=LEFT(A5,LEN(A5)-6))

expression, it will return the text ”855”. It’s a text value! We have to convert the text value to number format.

Excel remove numbers from right

Apply the below formula for the proper result:

=VALUE(LEFT(A5,(LEN(A5)-6)))

Remove the last nth characters using VBA

Because we are familiar with VBA programming, we are working on our main goal: to write better and better time-saving formulas for the Excel community.

Check this example below; it is easy to see how the formula is built.

Function RemoveLastNchar(rng As String, counter As Long)
RemoveLastNchar = LEFT(rng, LEN(rng) - counter)
End Function
Excel remove numbers from right

Delete the last nth characters from the right using an add-in

Use our DataXL productivity tools for Excel and follow these steps below:

  1. Locate the DataXL tab on the ribbon. Select the cell which contains the text.
  2. Click on the Text Tools icon. A new window will appear. Select the second tab, Delete.
  3. Enter the value.
  4. Example: If you want to remove the last six characters from the right, use 6 as a parameter.
Excel remove numbers from right

Additional resources and formula examples

  • Remove the first character in Excel
  • Remove file extension from filename
  • Split text and numbers

How do I remove 3 characters from right in Excel?

LEN(D5)-3 ▶ calculates the length of the text, “Jason Roy” and then subtracts the result with 3..
D5 ▶ refers to the cell address of the text “Jason Roy”..
=LEFT(D5,LEN(D5)-3) ▶ truncates the last 3 characters i.e. “Roy” from the text “Jason Roy”..

How do I remove 4 digits from right in Excel?

How to remove the last four digits in Excel.
Enter the data that you wish to trim the last digits..
Locate the cell where you want your result to be displayed. Click inside the cell. ... .
The next step is to enter the formula =LEFT(A1, LEN(A1)-1) in the output cell. ... .
Hit the enter key, and your result will show up immediately..

How do I remove text from the right of a character in Excel?

Delete texts before or after specific character by Find and Replace in Excel.
Select the cells you will remove texts before or after a specific character, press Ctrl + H keys to open the Find and Replace dialog..
Keep the Replace with text box empty, and then click the Replace All button..

How do you remove numbers from a cell in Excel?

Remove character from multiple cells using Find and Replace.
Select a range of cells where you want to remove a specific character..
Press Ctrl + H to open the Find and Replace dialog..
In the Find what box, type the character..
Leave the Replace with box empty..
Click Replace all..