Converting Text with a trailing CR to a Number

,Don't you just hate it when you import data and see the dreaded CR at the end of a number. You know that Excel automatically treats any cell content as text if it is a mix of numbers and text. This of course is a huge problem if you need to actually use that cell value in a calculation.  Below is a way to convert the content by using the LEN function and the LEFT function.

• The LEN sounds like a useless function but it actually very powerful - it counts the number of characters in a cell =LEN(text). 
• LEFT is a text function and it extracts the specified number of characters =LEFT(text, number of characters).

The LEN function counts the number of characters in the cell and the LEFT function tells Excel to extract everything except for the last 2 characters in that cell. I then multiplied it by -1 to make it negative.

So, in the example above, if you break it down, Excel looks at the LEN (A20)-2 and evaluates that as 5-2 which of course is 3 so now it would read =LEFT(A20,3)*-1. Excel would treat that as =100*-1 and the resulting answer would be -100.