Remove first character from string

How-to-remove-the-first-character-in-Excel

This tutorial will show various ways and tips on how to remove the first character from a cell in Excel using formulas.

In general, you can solve all text and string manipulation challenges with the help of custom formulas that are based on one or more built-in functions.

This article is a part of our definitive guide on Excel Formulas.

Table of contents:

  1. Remove the first character using the REPLACE function
  2. Use MID and LEFT functions to get the remaining part of a string
  3. RIGHT and LEN functions
  4. Apply a user-defined function (UDF)
  5. Use DataXL add-in
  6. Text-to-columns function

How to remove the first character in Excel?

Steps to remove the first character from a cell:

  1. Apply the =REPLACE(text, 1, 1, “”) formula where the arguments are:
  2. old_text: the cell that contains text
  3. start_num: character position = 1
  4. num_chars: no. of characters you want to replace = 1
  5. new_text: the text you want to replace = “”

Explanation: Using the setup mentioned above, the REPLACE function replaces the first character in cell B3 with an empty string “” and returns the result.

Formula:

=REPLACE(B3,1,1,””)

Remove the first n characters

Sometimes, you want to strip more than one character from the left of a string. In this case, use the following formula:

=REPLACE(B3,1,n,””)

remove-the-first-N-characters-from-a-string

In the formula, “n” is an arbitrary number. For example, if you want to remove the first five characters from the left, use the formula below:

=REPLACE(B3,1,5,””)

Get the remaining part of the string using MID and LEFT

We use the MID and LEFT functions in the example to extract the string from cell B3 without the first character.

=MID(B3,2,LEN(B3)-1)

Get-the-remaining-characters-using-MID-and-LEFT

Configure the MID function arguments in the following way:

  • text = “B3”
  • start_num = 2
  • num_chars = LEN(B3)-1

The formula will extract the text from the second character and return the part of a string.

Apply RIGHT and LEN Functions to remove the first character

You can combine the RIGHT and LEN functions to delete the first N characters from the left.

=RIGHT(string_cell, LEN(string_cell)-n_character)

Evaluate the formula from the inside out. First, the LEN function will return the length of the selected cell. After that, we extract the “text_lenght – 1” characters from the right using the RIGHT function.

using-RIGHT-and-LEN-functions-to-strip-the-first-character

In the example, you want to remove three characters from the left:

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

User-defined function to remove the first character

If you are familiar with VBA, you can write a simple function:

Function RemoveFirstNChar(rng As string, counter As Long)
RemoveFirstNChar = Right(rng, Len(rng) - counter)
End Function

Press Alt + F11 to open the VBA editor to implement the function, then insert a new Module.

Finally, paste the code and save the Workbook as .xlsm.

Use DataXL Productivity Suite

We have a powerful add-in section to extend the built-in function library.

  1. First, go to the DataXL tab on the ribbon. Then, select the cell or range of cells that contains the text.
  2. Click on the Text Tools icon. A new window will appear.
  3. Select the Delete Tab and enter a number value, in this case, 1.
excel-text-tools-add-in

Use Text-to-columns function

You can split a single text column into multiple parts using the Text-to-Columns function.

First, click on the Ribbon and locate the Data Tab.

text-to-columns-2

After clicking the Text-to-Columns icon, a new window will appear:

convert-text-to-columns-wizard-step-1

Choose the “Fixed width” option, then click “Next”.

Move the cursor over the “Data Preview” area to manually separate the string’s first character.

separate-the-first-character-of-the-string-manually

Click next to change the data format, or click “Finish” to perform the action.