They could be used to solve this problem, but they are more work to configure, so I don't see any advantage to using them. It is worth noting that Excel contains two other functions, the SEARCH function and the FIND function that are meant to look for a substring in a text value. The case-sensitive version of the formula works the same way: =IF(EXACT(RIGHT(B5,3),"JWB"), "Yes", "No") For example, to return "Yes" when a cell ends with "jwb" and "No" when not, you can use a formula like this =IF(RIGHT(B5,3)="jwb", "Yes", "No") To adapt the formulas above to "If cell ends with", simply drop the formulas into the IF function as the logical test. Note that we don't need the equal to operator (=) in this formula because EXACT performs a comparison automatically. The second formula returns TRUE because "JWB" does equal "JWB" taking into account case. The first formula returns FALSE because the EXACT function is case-sensitive, so "JWB" does not equal "jwb". =EXACT(RIGHT(B6,3),"JWB") // returns TRUE Turning back to cell B6 in the worksheet shown, the two formulas below return different results: =EXACT(RIGHT(B6,3),"jwb")// returns FALSE For example: =EXACT("abc","ABC") // returns FALSE For example, to test for a value that ends with "apple", num_chars should be set to 5: =RIGHT(B5,5)="apple"Įxcel is not case-sensitive by default, but you can easily adapt the formula to use the EXACT function to make the formula case-sensitive like this: =EXACT(RIGHT(B5,3),"JWB")ĮXACT takes two arguments, text1 and text2. EXACT will only return TRUE when text1 and text2 are exactly the same, taking into account case. However, this value needs to be modified to suit the situation. Also note the num_chars argument is set to 3 above because we want to work with the last 3 letters in the cell only. Note that Excel is not case-sensitive by default, so "JWB"="jwb" will return TRUE in a formula. In cell B6, however, the result is TRUE, since "XYZ-6543-JWB" does end with "jwb". The RIGHT function extracts the last 3 characters in cell B5 and the result is compared to the string "jwb", forcing a TRUE or FALSE result. The formula is solved like this: =RIGHT(B5,3)="jwb"įor cell B5 the result is FALSE, since "ABC-1224-HNN" does not end with "jwb". This means we can use the RIGHT function to test if cell B5 ends with "jwb" like this: =RIGHT(B5,3)="jwb" For example, the formula below returns the last three letters of "apple", which is "ple": =RIGHT("apple",3) // returns "ple" The RIGHT function extracts a given number of characters from the right side of a text string. This problem can be solved with the RIGHT function, as explained below. In this example, the goal is to test values in column B to see if they end with a specific text string, which is "jwb" in the worksheet shown.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |