Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Remove characters from left side of a cell

While working with Excel, we are able to retrieve only the specific parts of a text string that we want by using the REPLACE, RIGHT or LEN  functions.  This step by step tutorial will assist all levels of Excel users in removing characters from the left side of a cell using two different methods.  

Figure 1. Final result: Remove characters from left side of a cell

Formula 1:  =REPLACE(C4,1,D4,"")  

Formula 2:  =RIGHT(C4,LEN(C4)-D4)  

Syntax of the REPLACE function

REPLACE function changes a specific part of a text string with a different text string, based on the number of characters we specify

=REPLACE(old_text, start_num, num_chars, new_text)

The parameters are:

  • old_text  – any text that we want to change or modify
  • start_num – the starting position of the character in old_text that we want to replace with new_text
  • num_chars – the number of characters in old_text that we want to replace with new_text
  • new_text – the text that will replace the characters in old_text

Syntax of the RIGHT function

RIGHT extracts a substring from the right side of a text string

=RIGHT(text,[num_chars])

  • text – the text string containing the characters or substring we want to extract  
  • num_chars   Optional; determines the number of characters we want to extract

Syntax of the LEN function

LEN returns the length of a text string in number of characters

=LEN(text)

  • text  – the text whose length we want to determine. Spaces are counted as characters.

Setting up Our Data

Our table contains a list of Personnel ID (column B), Code (column C) and num_chars (column D).  Num_chars contain the number of characters that we want to remove from the left side of the Code.  The resulting text string will be recorded in column E under “Result”.  

Figure 2. Sample data to remove characters from left side of a cell

Remove characters from left side using REPLACE

We only want specific parts of the Code in column C.  In order to remove characters from the left side, we use the REPLACE function.  Let us follow these steps:

Step 1.  Select cell E4

Step 2.  Enter the formula: =REPLACE(C4,1,D4,"")

Step 3:  Press ENTER

Step 4:  Copy the formula in cell E4 to cells E5:E7 by clicking the “+” icon at the bottom-right corner of cell E4 and dragging it down

Old_text is the code in column C, while start_num is 1 and num_chars is D4 or 7.  Our formula replaces the unwanted characters with an empty string “”, starting from the first character up to the seventh character.  

As a result, the first seven characters are removed, leaving the substring “ACC01” in cell E4.  

Figure 3. Entering the formula using REPLACE

Copying the formula to the succeeding cells populates column E with the substrings we want as shown below.  

Figure 4. Output: Remove characters from left side of cell

Remove characters from left side using RIGHT and LEN

There is an alternative solution to remove characters from the left side using the RIGHT and LEN functions.  Let us follow these steps:

Step 1.  Select cell E4

Step 2.  Enter the formula: =RIGHT(C4,LEN(C4)-D4)

Step 3:  Press ENTER

Step 4:  Copy the formula in cell E4 to cells E5:E7 by clicking the “+” icon at the bottom-right corner of cell E4 and dragging it down

Our formula extracts a substring from the right side of the text in C4.  The number of characters is determined by the LEN function LEN(C4)-D4.  The length of C4 is 12 characters, and D4 is 7.  Hence, the number of characters we want to extract from the right side is (12 – 7) or 5.  

As a result, the first seven characters are removed, leaving the five characters at the right side of C4 which is “ACC01”.  The results are the same as in the previous method using REPLACE.  

Figure 5. Entering the formula using RIGHT and LEN

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I am having issues creating the one way data table to show the means and standard deviations when you change the proportion of portfolio 1. I have the mean formula and the standard deviation formula at the top and -1 through 1 down the left side. I highlight all of it and create a data table. I reference the "proportion of portfolio 1" cell in the column input box but when the data table comes out it has all the same numbers all they way down the table.
Solved by G. Q. in 49 mins
I have 2 columns one of them with a dropdown list of 20 and the other one with 2 options only. I want that when Comment A and B are chosen in the first column then the secon column populate "X" STRAIGHT AWAY. HOW DO i DO IT?
Solved by I. D. in 10 mins
What formula do I use to create a running balance across several worksheets as the numbers continue to change? For example. I have created a drop down list for every month of the year from Jan - Dec for people to sign up for sessions. As they sign up for sessions, I want to create a list beside their names to show how many sessions they have signed up for. So if they sign up for 6 the summary should automatically display 6, if they sign up for 1 more it would instantly show 7 and so on for each person? Make sense
Solved by C. F. in 59 mins
What is the formula for splitting up one column text into 2 columns?
Solved by B. H. in 20 mins
I have text and number in multiple cells (Ex: Cell 1: 4S, Cell 2: 3.5A, Cell 3: 8O) and want to sum the number values only (Ex: 4 + 3.5 + 8). How can I do this?
Solved by O. A. in 60 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc