Removing characters from Excel cells

Sometimes Excel cells contain unwanted data. Removing this data can be a very tedious job. For example, I recently received a data file where the first column cells contained some numeric identifiers. The problem was that the number were typed as #123456, #123457, etc. I only needed the numbers without # symbol for my software to process this data. Since it was not possible for me to manually edit over 20000 rows, I used the following code:

=right(A1,len(A1)-1)

The cell we need to edit is A1. len(A1) returns the length of the text in the cell. The right function removes characters from the beginning of the string. len(A1) – 1 specifies the size of the new string, which is one character less than the original, so the # symbol is removed from the string. Next, we simply drag down the selector to apply this formula to every row.

There is also a left function which removes characters from the end of the string.

=right(A1,len(A1)-2)   removes first 2 characters
=left(A1,len(A1)-2)    removes last 2 characters

You can also use the search replace command to edit text.