How to remove x characters from left of a cell in Excel

Goal: Use Excel functions to remove x number of characters from the left of a cell.

This video will present a non-macro approach to remove x characters from left of a cell in Excel. In this example, we are unable to sum a range of cells, because they are mixed string values i.e. CAD 28.31, CAD 55.16 and so.

Clearly we can sum the values in the range in column H, but we can’t do the same for the corresponding cells in column I.

We can sum the range in column H, but not in column I because these cells are mixed strings.

One solution is write a formula to extract a specified number of characters from the left of each cell.

How to remove x characters from left

As mentioned, we can use a suite of native Excel functions, in order to extract a specified number of characters from the left of a cell in Excel.

First type the VALUE function because we want the output value to be an integer. Then add the RIGHT function. For the num_chars syntax use the LEN function and again reference the desired cell.

First use the VALUE function, then the RIGHT function and finally the LEN function.

We want to remove the string “CAD “, which is 4 characters – therefore we enter negative 4. Hence we should return a value of 28.31. We can fill down this formula against the other adjacent cells in column I.

As we can see, we are returning the correct output values.

Let’s try it again!

Let’s walk through this formula again. Firstly, insert the VALUE function. Then the RIGHT function.

After referencing the underlying cell address, add the LEN function to the formula. As we want to eliminate 4 characters from the left of the value, we need to type in negative 4.

In summary, here is the example workbook which shows you how to extract four characters from the left of a cell in Excel.

Download SpreadsheetVault – Remove x characters from left of cell (solution).xlsb