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.