How To Remove Numbers In Excel From The Left

When working with unstructured text data in your worksheets, you oft need to parse it to retrieve relevant information. This article volition teach y'all a few simple ways to remove any number of characters from the left or right side of a text cord.

How to remove left characters
- Delete first character from a string
- Strip characters from left
- Custom role to remove first northward characters
How to remove right characters
- Delete last graphic symbol from a string
- Strip characters from correct
- Custom part to delete concluding n characters
How to delete characters on both sides of a cord
Get the result equally number
Delete start or terminal graphic symbol with Flash Make full
Remove characters by position with Ultimate Suite

How to remove characters from left in Excel

Removing outset characters from a string is ane of the most common tasks in Excel, and information technology can be accomplished with 3 different formulas.

Remove first character in Excel

To delete the showtime graphic symbol from a string, you can utilize either the Replace role or a combination of Right and LEN functions.

Supersede(string, 1, 1, "")

Hither, we simply have one graphic symbol from the starting time position and replace it with an empty string ("").

RIGHT(string, LEN(cord) - one)

In this formula, we use the LEN function to calculate the total length of the string and subtract 1 grapheme from it. The divergence is served to RIGHT, so information technology extracts that many characters from the cease of the string.

For example, to remove the commencement character from cell A2, the formulas become as follows:

=REPLACE(A2, 1, 1, "")

=RIGHT(A2, LEN(A2) - 1)

Removing the first character from a string

Remove characters from left

To remove leading characters from the left side of a cord, you lot too employ the Supplant or RIGHT and LEN functions, but specify how many characters you want to delete every fourth dimension:

REPLACE(cord, one, num_chars, "")

RIGHT(cord, LEN(string) - num_chars)

For instance, to remove start ii characters from the string in A2, the formulas are:

=REPLACE(A2, 1, 2, "")

=Right(A2, LEN(A2) - two)

To remove first iii characters, the formulas have this class:

=REPLACE(A2, 1, iii, "")

=RIGHT(A2, LEN(A2) - 3)

The screenshot below shows the REPLACE formula in action. With Right LEN, the results would be exactly the same.
Removing the specified number of characters from left

Custom function to delete first northward characters

If you don't listen using VBA in your worksheets, you can create your ain user-defined function to delete characters from the beginning of a cord, named RemoveFirstChars. The office's code is as simple as this:

Function RemoveFirstChars(str As String, num_chars As Long) 	RemoveFirstChars = Right(str, Len(str) - num_chars) Finish Function

Once the lawmaking is inserted in your workbook (the detailed instructions are hither), you tin remove first n characters from a given cell past using this meaty and intuitive formula:

RemoveFirstChars(string, num_chars)

For example, to delete the showtime character from a string in A2, the formula in B2 is:

=RemoveFirstChars(A2, one)

To strip commencement two characters from A3, the formula in B3 is:

=RemoveFirstChars(A4, two)

To delete outset three characters from A4, the formula in B4 is:

=RemoveFirstChars(A4, 3)

Custom function to remove first characters from a cell

How to remove characters from right

To remove characters from the right side of a string, yous can also use native functions or create your own 1.

Remove last character in Excel

To delete the terminal grapheme in a cell, the generic formula is:

LEFT(string, LEN(string) - 1)

In this formula, you subtract 1 from the total string length and pass the difference to the LEFT function for it to extract that many characters from the beginning of the string.

For instance, to strip the concluding character from jail cell A2, the formula in B2 is:

=LEFT(A2, LEN(A2) - ane)

Removing the last character in Excel

Remove characters from right

To strip off a given number of characters from the finish of a jail cell, the generic formula is:

LEFT(string, LEN(string) - num_chars)

The logic is the aforementioned as in the to a higher place formula, and beneath are a couple of examples.

To remove the last 3 characters, use 3 for num_chars:

=LEFT(A2, LEN(A2) - 3)

To delete the last 5 characters, supply 5 for num_chars:

=LEFT(A2, LEN(A2) - five)

Removing a specified number of characters from right

Custom part to remove last n characters in Excel

If you lot'd similar to have your own part for removing any number of characters from correct, add this VBA code to your workbook:

Function RemoveLastChars(str As Cord, num_chars As Long) 	RemoveLastChars = Left(str, Len(str) - num_chars) End Function

The function is named RemoveLastChars and its syntax hardly needs any explanation:

RemoveLastChars(string, num_chars)

To give it a field exam, allow'due south become rid of the final graphic symbol in A2:

=RemoveLastChars(A2, one)

Additionally, nosotros'll remove the last ii characters from the correct side of the string in A3:

=RemoveLastChars(A3, 2)

To delete the last 3 characters from cell A4, the formula is:

=RemoveLastChars(A4, three)

As y'all tin see in the below screenshot, our custom function works brilliantly!
Deleting the last n characters from string

How to remove characters from right and left at once

In situation when you need to wipe out characters on both sides of a string, you can either run both of the above formulas sequentially or optimize the chore with the help of the MID function.

MID(string, left_chars + 1, LEN(cord) - (left_chars + right_chars)

Where:

chars_left - the number of characters to delete from left.
chars_right - the number of characters to delete from right.

Suppose you want to extract the username from a string like mailto:Sophia@gmail.com. For this, part of text needs to be removed from the beginning (mailto: - 7 characters) and from the end (@gmail.com - eleven characters).

Serve the higher up numbers to the formula:

=MID(A2, seven+one, LEN(A2) - (7+10))

…and the result won't keep you waiting:
Remove characters from both sides of a string

To empathize what's actually going on here, permit's recall the syntax of the MID function, which is used to pull a substring of a sure size from the middle of the original cord:

MID(text, start_num, num_chars)

The text argument does not raise whatever questions - it'south the source string (A2 in our case).

To go the position of the first character to extract (start_num), you add 1 to the number of chars to be stripped off from left (7+1).

To determine how many characters to return (num_chars), you summate the total of removed characters (7 + 11) and decrease the sum from the length of the unabridged string: LEN(A2) - (seven+x)).

Get the result equally number

Whichever of the above formulas yous use, the output is always text, even when the returned value contains merely numbers. To return the upshot equally a number, either wrap the core formula in the VALUE role or perform some math operation that does not touch on the effect, e.yard. multiply by 1 or add 0. This technique is especially useful when you desire to calculate the results farther.

Suppose y'all've removed the beginning grapheme from cells A2:A6 and want to sum the resulting values. Astonishingly, a trivial SUM formula returns nil. Why'due south that? Evidently, because yous are adding up strings, non numbers. Perform ane of the below operations, and the result is fixed!

=VALUE(Replace(A2, 1, 1, ""))

=RIGHT(A2, LEN(A2) - 1) * 1

=RemoveFirstChars(A2, 1) + 0

Remove characters and get the result as number

Remove outset or last grapheme with Flash Make full

In Excel 2022 and later versions, there is one more like shooting fish in a barrel way to delete the outset and last characters in Excel - the Flash Fill feature.

In a cell adjacent to the commencement prison cell with the original information, blazon the desired result omitting the first or last graphic symbol from the original string, and press Enter.
Offset typing the expected value in the next cell. If Excel senses the pattern in the data you are entering, it will follow the aforementioned pattern in the rest of the cells and display a preview of your data without the first / last graphic symbol.
Just striking the Enter fundamental to accept the preview.

Delete first or last character with Flash Fill

Remove characters by position with Ultimate Suite

Traditionally, the users of our Ultimate Suite can handle the task with a few clicks without having to remember a handful of various formulas.

To delete the first or terminal n characters from a string, this is what you need to do:

On the Ablebits Data tab, in the Text grouping, click Remove > Remove by Position.
On the add-in's pane, select the target range, specify how many characters to delete, and hit Remove.

For instance, to remove the first grapheme, we configure the post-obit pick:
Remove the first character from selected cells

That'south how to remove a substring from left or right in Excel. I thank you for reading and look forwards to seeing you on our blog next calendar week!

Bachelor downloads

Remove first or last characters - examples (.xlsm file)
Ultimate Suite - trial version (.zip file)

You may as well be interested in