Use the LEFT function to convert invalid numbers to valid numbers - Text Functions - Microsoft Excel Tutorials

2.  Use the LEFT function to convert invalid numbers to valid numbers

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->.

Watch the Video Tutorial - https://www.youtube.com/watch?v=KLtzGBRucvw

Hope You Like This Tutorial - Give Comments


http://worldoftutorial.blogspot.com/