A common requirement is how to remove characters in Excel cells e.g. remove a comma from a number. There is no REMOVE function in Excel but this can be easily done with another function.

The key is to use the SUBSTITUTE function. Although you want to ‘remove’ a character, another way to express it is to substitute the character/s with nothing.

For example lets say we have a number like 1,000,000.00.

On my computer the commas cause the number to be seen as text and I cannot add them up. In this case I can use the SUBSTITUTE function. Assuming the number is in cell A1 the formula would be:

=SUBSTITUTE(A1,”,”,“”)

It is important to note the the last part (the “”). Notice that they are flush against each other with no space in between. In Excel ” ” is different from “”. The first one will put in a space. The second one will put in nothing.

The end result of this formula will be 1000000.00 .

This may not be the end as, depending on your computer settings, it may still see the number as a text. You can use the VALUE function to fix this.

The above is a formula based way to correct the issue. If you would rather use a tool, use the FIND/ REPLACE tool.

The logic is the same but remember in the ‘replace’ part to use nothing, i.e. don’t click in it and hit the space bar otherwise it will insert a space instead of nothing.

Related

Find space from right in Excel cell

Using multiple characters as delimiters in Excel Text to Column