This post will guide you how to extract all characters except for the first N or Last n characters from a given text string with a formula in Excel. How do I get all characters but the first or last character from a text in Excel 2013/2016/2019/365.

Assuming that you have a list of data in range of Cells which contain text String values. And you wish to remove the first character or Last character or extract all characters except for the first character. You can achieve the result based on the MID function, the LEFT function and the LEN function.

1. Extract All Characters Except the First Character Using Formula

If you want to extract all characters except for the first character, you just need to create a new formula based on the MID function and the LEN Function. Like this:

=MID(B1,2,LEN(B1))

Type this formula into a blank cell or the adjacent cell C1, and press Enter key to apply it. And then drag the Auto Fill handle down to other cells to apply this formula to extract characters.

Let’s see how this formula works:

The Cell B1 is the cell that you want to extract all characters but the first one character. And the number 2 is the starting position that you want to extract. And the LEN function will return the length of text string in Cell B1. So this formula can be used to extract all characters from Cell B1 except for the first character. If you want to extract all characters but the first N characters, you just need to change number 2 to N+1.

2. Extract All Characters Except the First Character Using User Defined Function

To extract all characters except the first character from a cell in Excel using a user defined function with vba code, you can use the following steps:

Step1: Press Alt + F11 to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, click on Insert > Module to create a new module.

Step3: Copy and paste the VBA code into the module. Close the Visual Basic Editor.

Function ExtractExceptFirst(str As String) As String
    ExtractExceptFirst = Mid(str, 2, Len(str) - 1)
End Function

Step4: In your Excel worksheet, select the cell where you want to use the custom function. Type the following formula into the formula bar.

=ExtractExceptFirst(B1)

The cell B1 that contains the text you want to extract characters from.

Step5: Press Enter to run the custom function.

The output of the custom function will be displayed in the cell where you entered the formula.

3. Extract all Characters Except the Last Character Using Formula

If you want to extract all characters except for the last character, you need to use a formula based on the LEFT function in combination with the LEN function. Like this:

=LEFT(B1,LEN(B1)-1)

Type this formula into a blank cell or cell D1, and press Enter key to apply it. And then drag the Auto Fill handle down to other cells to apply this formula to extract characters.

Note: The number 1 indicates that you want to remove the last character from the given text string in Cell B1.

4. Extract all Characters Except the Last Character Using User Defined Function

To extract all characters except the last character from a cell in Excel using a user defined function, you can use the following VBA code:

Function ExtractExceptLast(str As String) As String
    ExtractExceptLast = Left(str, Len(str) - 1)
End Function

You just need simply enter the formula into a blank cell:

=ExtractExceptLast(B1)

Where “B1” is the cell containing the text you want to extract characters from.

This function will return all characters in the text except for the last character.

5. Video: Extract All Characters Except the First Character

This video will demonstrate how to extract all characters except the first or the last character from a cell in Excel using a formula or a User Defined Function with VBA code as the formula.

6. Related Functions

• Excel MID function
  The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
• Excel LEFT function
  The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…
• Excel LEN function
  The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…