Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'

 

 

Remove characters in excel cells

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.

Find space from right in Excel cell

Using multiple characters as delimiters in Excel Text to Column