How to use the LEFT function to convert invalid numbers to valid numbers in Excel

The LEFT function in Excel returns a specified number of characters from the left side of a text string. For example, =LEFT("123456",3) returns “123”.

We can use the LEFT function to convert invalid numbers to valid numbers by extracting the numeric part of the text string and ignoring the non-numeric part. For example, if we have a text string like “123.45 USD”, we can use the LEFT function to get “123.45” as a valid number.

Procedures

To use the LEFT function to convert invalid numbers to valid numbers, we need to follow these steps:

1. Identify the position of the first non-numeric character in the text string. We can use the FIND function or the SEARCH function to do this. For example, =FIND(" ", "123.45 USD") returns 7, which is the position of the space character in the text string.
2. Subtract 1 from the position of the first non-numeric character to get the number of characters to extract from the left side of the text string. For example, =FIND(" ", "123.45 USD")-1 returns 6, which is the number of characters to extract from the left side of the text string.
3. Use the LEFT function with the text string and the number of characters to extract as arguments. For example, =LEFT("123.45 USD", FIND(" ", "123.45 USD")-1) returns “123.45”.
4. Use the VALUE function to convert the text string returned by the LEFT function to a valid number. For example, =VALUE(LEFT("123.45 USD", FIND(" ", "123.45 USD")-1)) returns 123.45 as a valid number.

Explanation

The LEFT function can be used to convert invalid numbers to valid numbers in Excel by extracting the numeric part of the text string and ignoring the non-numeric part. This is useful when we have text strings that contain numbers with different currency symbols, units, or other characters that prevent Excel from recognizing them as valid numbers.

The syntax of the LEFT function is =LEFT(text, [num_chars]), where text is the text string to extract from, and num_chars is the number of characters to extract from the left side of the text string. If num_chars is omitted, the LEFT function returns the first character of the text string.

To use the LEFT function to convert invalid numbers to valid numbers, we need to specify the number of characters to extract from the left side of the text string that correspond to the numeric part of the text string. To do this, we need to identify the position of the first non-numeric character in the text string, and subtract 1 from it. We can use the FIND function or the SEARCH function to find the position of the first non-numeric character in the text string. The syntax of the FIND function is =FIND(find_text, within_text, [start_num]), where find_text is the text to find, within_text is the text to search within, and start_num is the position to start the search from. The syntax of the SEARCH function is similar, except that it is not case-sensitive. For example, =FIND("a", "Apple") returns 1, while =SEARCH("a", "Apple") returns 5.

Once we have the number of characters to extract from the left side of the text string, we can use the LEFT function with the text string and the number of characters as arguments. This will return the numeric part of the text string as a text string. To convert the text string to a valid number, we can use the VALUE function, which converts a text string that represents a number to a number. The syntax of the VALUE function is =VALUE(text), where text is the text string to convert to a number. For example, =VALUE("123.45") returns 123.45 as a number.

Example

Suppose we have a list of text strings that contain numbers with different currency symbols, such as “123.45 USD”, “456.78 EUR”, and “789.01 GBP”. We want to convert these text strings to valid numbers in Excel, so that we can perform calculations on them.

To do this, we can use the LEFT function to extract the numeric part of the text string, and the VALUE function to convert the text string to a number. We can also use the FIND function or the SEARCH function to find the position of the first non-numeric character in the text string, and subtract 1 from it to get the number of characters to extract from the left side of the text string.

Here is an example of how we can do this in Excel:

The result of the scenario is that we have converted the text strings to valid numbers in Excel, which we can use for further calculations.

Other approaches

Another approach to convert invalid numbers to valid numbers in Excel is to use the REPLACE function to replace the non-numeric part of the text string with an empty string, and then use the VALUE function to convert the text string to a number. The syntax of the REPLACE function is =REPLACE(old_text, start_num, num_chars, new_text), where old_text is the text to modify, start_num is the position to start the replacement from, num_chars is the number of characters to replace, and new_text is the text to replace with. For example, =REPLACE("123.45 USD", 7, 3, "") returns “123.45”.

To use the REPLACE function to convert invalid numbers to valid numbers, we need to specify the position and the number of characters to replace with an empty string. We can use the FIND function or the SEARCH function to find the position of the first non-numeric character in the text string, and use the LEN function to find the length of the text string. The syntax of the LEN function is =LEN(text), where text is the text to get the length of. For example, =LEN("123.45 USD") returns 10.

Once we have the position and the number of characters to replace, we can use the REPLACE function with the text string, the position, the number of characters, and an empty string as arguments. This will return the numeric part of the text string as a text string. To convert the text string to a valid number, we can use the VALUE function, as before.

Here is an example of how we can do this in Excel:

The result of the scenario is the same as before, but using a different approach.