USE THE LEFT FUNCTION TO CONVERT INVALID NUMBERS TO VALID NUMBERS USING MICROSOFT EXCEL

In this example, invalid numbers have to be converted to valid numbers. The invalid numbers contain a minus sign at the right end of the text. Excel cannot interpret this, so the last digit of the text needs to be moved to the left of the numbers. First, check the length of each number with the LEN function. This function returns the number of characters in a text string. Then use the LEFT function to move the minus sign.

LEN(text)

text: The text whose length you want to be determined. A space is considered a character.

To cut off the last digit and display a negative value:

1. In a worksheet, enter a series of numbers in cells A2:A10 that have a minus sign at the end.
2. Select cells B2:B10 and type the following formula: =-LEFT(A2,LEN(A2)-1).
3. Press <-Ctrl+Enter->.

USE THE LEFT FUNCTION TO CONVERT INVALID NUMBERS TO VALID NUMBERS USING MICROSOFT EXCEL