How to Remove First Character in Excel: 6 Top Ways

ByJames Corrigan

You may need to remove the first character, last character, or specific character in a string, and delete a character from a string if the condition is True.

Removing the first character from a string is a common task in data processing.

For example, You may receive the currency data, which prefixes the currency symbol. You need to remove the symbol to apply aggregate functions.

This blog post covers how to remove the first character in a string using.

  • REPLACE, RIGHT, MID, and LEN functions to get the first character.
  • Flash Fill feature to delete the first character in Excel.
  • Power Query method to get rid of the first character.
  • VBA and Office Scripts methods to delete the first character.
  • Python in Excel to remove the first character.

In this article,  you will learn a few easy methods to remove the first character in Excel. Get your copy of the example workbook used in this blog post to follow along!

Remove the First character with Excel functions

Suppose you have received a data set where the field EmpID starts with a region character. You need to remove the first character from the data for further analysis.

You can use either the combination of RIGHT or MID with LEN functions or the REPLACE function to remove the first character from a string in Excel.  In this method, whenever you add new records to your data, you must copy the same formula for the additional rows.

Use RIGHT or MID and LEN

Type the formula =RIGHT( A2, LEN( A2 ) – 1 ) in cell B2 and drag it to fill the range of cells B2:B21.

You use the LEN function to calculate the length of the string in cell A2 and subtract 1 from it then the RIGHT function returns the number of characters from the right side of the string.

Alternatively, you can use the MID function instead of the RIGHT function in your formula to delete the first character.

=MID( A2, 2, LEN( A2 ) )

Use Replace function

The REPLACE function replaces the first character with an empty string. It is an easy method to remove the first character in an Excel cell.

Type the below formula = REPLACE( A2, 1, 1, "") in cell B2 and drag it to fill the range of cells B2:B21.

Remove the First character with the Auto Fill feature

You will explore how to use the Excel Flash fill feature to remove the first character in Excel.

Just start entering the number in cells B2 and B3. Excel will suggest the pattern based on your entries in cells B2 and B3. Press Enter to fill in the values for the rest of the cells.

It works as a static method; you need to start from scratch whenever you add data in Excel rows.

Remove the First character in Excel with Power Query

You can easily load, transform, and format your data using the Power Query user interface. It allows you to connect multiple data files, tables, and text files.

Steps to delete the first character in Excel using Power Query.

  1. Select the range of cells A1:A21 in the Data Sheet
  2. Choose From Table/Range option in the Data Tab – to open the Power Query window.
  3. Rename the Query name PowerQuery
  1. Select By Number of Characters in the Split Column dropdown.
  1. Type 1 in the Number of characters input box
  2. Choose the option Once, as far left as possible, since you need to remove only the first character.
  3. Press the OK button. you can see two columns in your power query table, the first character is in first column EmpID.1 and the remaining characters are in second column EmpID.2
  1. Rename the column EmpID.2 and remove the first column.
  2. Select the Close & Load command and Press Close & Load To… – to popup Import Data dialog box.
  1. In the Import Data dialog box, select the Table option, select New worksheet to insert the transformed Power Query table into a new worksheet, and Press OK.

Remove First Character in Excel with VBA

If you need to remove the first character in Excel text in your automation project. You can go for this VBA method. The Macro uses For Each loop to iterate the range of cells and the REPLACE with LEFT functions to remove the character.

Sub RemoveFirstCharacter()
' Declare variables
Dim rge As Range
' Assign range of cells
Set rge = Selection
' loop through each cell in the range
For Each rg In rge
    ' Use Replace function to remove first character and assign to adjacent cell
    rg.Offset(0, 1).Value = Replace(rg.Value, Left(rg.Value, 1), "")
Next
End Sub

Insert the above code into your VBE.

Macro declares the variable rge and assigns the selected range.  Loop through the selected range of cells using For Each. In each iteration, the LEFT function returns the first character, the REPLACE function replaces the first character with an empty string and stores it in an adjacent cell.

If you need to remove the last character, you can use the RIGHT function instead of LEFT in the below line of code in your VBA Script.

    rg.Offset(0, 1).Value = Replace(rg.Value, Right(rg.Value, 1), "")

To run the RemoveFirstCharacter macro.

Select the range of cells A2:A21. Press Alt + F8, the keyboard shortcut to open the Macro dialog box, Choose the RemoveFirstCharacter Macro, and then Press Run.

Remove First Character in Excel with Office Scripts

Office scripts allow you to record repetitive tasks, automate, edit the existing code, and run in both desktop and web Excel versions.

In this post, you will explore the Office Scripts For loop and substring function to delete the first character.

function main(workbook: ExcelScript.Workbook) {

    // Assign active worksheet to the variable
    let currSheet = workbook.getActiveWorksheet()

    // Get selected range
    let rng = workbook.getSelectedRange();

    // Store Number of Rows 
    let rows = rng.getRowCount();

    // Loop through the selected range of cells
  for (let i = 0; i < rows;  i++) {

        // Assign the cell value to the variable cellValue as a string
        let cellValue = rng.getCell(i, 0).getValue() as string;

        // use substring() function to remove the first character and return all other characters. Then assign to the adjacent cell
        rng.getCell(i, 1).setValue(cellValue.substring(1))
        
    }
}

The script assigns the selected range of cells to the variable rng. For loop iterates each cell in the selected range.

Script stores the current cell value to the variable cellValue, and uses the substring function to delete the first character and store it in the adjacent cell.

Follow the below steps to execute the Remove First Character Office script in Excel.

  1. Select the cells A2:A21
  2. Go to the Automate menu.
  3. Select the Remove First Character Script – to open the Code editor on the right side of your Excel application.
  4. Press Run 

Remove the First Character in Excel with Python

Python in Excel is available on Beta channel. Python is a high-level programming language. It is easy to read and learn.

You can compare the Python script and VBA or OS to remove the first character. Compared with other scripts it is easy and only two lines of code.

It runs on Microsoft Cloud. So, you do not need to install Python software and deploy the project on a client machine.

Steps to run Python in Excel

  1. Select the cell B2.
  2. Click the Insert Python command in the Formulas Tab Python (Preview) groups.
  1. You can see the PY in the formula bar. It indicates that you are writing Python script. Copy the below Python script in the formula bar.
df = xl("Python!$A$2:$A$21")
df = [x[1:] for x in df[0]]
  1. Press the key Ctrl + Enter to commit Python code in Excel. Default, it will return a Python list object. You need to convert it to Excel values.
  1. To convert Excel Value, Choose the Excel Value option in the formula bar Python Output button drop-down.

Python Script Explanation

xl("Python!$A$2:$A$21") function assigns the Excel range of cells A1:A21 to a Python dataframe df.

Python index starts from 0, df[0] refers to the first column in the data frame df. The script for x in df[0], iterates through each item in the df[0] column.

String values are stored array of characters in Python. In your Python script, x = E331 store as a list [ E, 3, 3, 1 ].

You can access each character in the list using the index starting from 0. x[0] = E; x[1]=3; x[2]=3; and x[3]=1.  x[1:] returns all the characters in the string starting from the second item in the list 331, which excluded the first character from the string.

Conclusions

The data you access from different data sources and need to clean the data for your analysis. You must know the basic text processing tasks such as replacing, adding, removing specific characters, splitting, and joining text data.

In this post, you learned the easy method, use the REPLACE function to remove the first character in Excel.  Alternately you can use a combination of functions RIGHT or MID with LEN.

Flash fill feature helps you to delete the first character and it will not store any additional formula in the cell. The Power Query method works well when dealing with multiple data sources or large data sets.

VBA or Office Scripts code can be used for your dashboard or report automation projects.

Finally, the Python script for data science or machine learning models.

Do you know any other method to remove the first character in Excel? Let me know in the comments below!

Similar Posts