The COUNTA function, how to use the function to count cells containing data in Excel
The LEFT function has a syntax of = LEFT (text; [num_chars]) . Inside:
In the table below, use LEFT function to find the first 3 characters in cell B2. Enter the formula = LEFT (B2.3) and press Enter.
The result is that we get 3 characters from left to right of the character string in cell B2.
Or in the input formula, you can replace the position of the cell containing the string with the character and enclosed in the quotation mark as shown.
When combining these two functions together, we will use it to search for a string of characters preceded by a certain character, such as taking the last name in the full name column, taking the country code except the phone number. In the column name are separated by spaces, so we use the formula = = LEFT (B2, SEARCH ("", B2) -1) and then press Enter.
Then -1 to not extract space characters when searching for characters.
The result you get is the character string in the box. Scroll down to the boxes below to get more results.
For the phone number range that you want to get the country code before the dot, enter the formula = LEFT (B5, SEARCH (".", B5) -1) and press Enter.
The result will only get the country code in the phone number.
LEN functions are very useful in combination with the string finder functions. With LEFT function when adding LEN function to remove certain characters from the end of the string. Combination formula = LEFT (text, LEN (text) - character to move).
The LEN function takes the total number of characters in a string, then subtracts the number of characters to remove from the total length of the sequence. The LEFT function will return the remaining number of characters.
For example, remove the 5 characters of the string in cell B2, enter the formula = LEFT (B2, LEN (B2) -5) and press Enter.
As a result, we have the remaining characters when we have removed the last 5 characters in the character string, including spaces.
When these two functions work together, it returns the numeric character, instead of the text string as in the LEFT function. For example, export the first 2 characters of the string in cell B5, enter the formula = VALUE (LEFT (B2.2)).
The result is the number to look for as shown.
Above is how to use the LEFT function to get the character string from the left and examples when combining LEFT function with other functions. If an error occurs, the user needs to check if num_chars is greater than 0.
I wish you all success!