Thus, you have the num_chars argument: SEARCH (" ", A2, SEARCH (" ",A2)+1) - SEARCH (" ",A2) To find out the number of characters to return, subtract the position of the 1 st space from the position of the 2 nd space, and then subtract 1 from the result since you don't want any extra spaces in the resulting substring. Next, get the position of the 2 nd space character by using nested Search functions that instruct Excel to start searching from the 2 nd occurrence of the space character: SEARCH(" ",A2,SEARCH(" ",A2)+1).Thus, you get the start_num argument of your Mid formula: SEARCH(" ",A2)+1 Like in the previous example, use the SEARCH function to determine the position of the first space (" "), to which you add 1 because you want to start with the character that follows the space.Technically, the task boils down to working out the positions of two spaces in the original string, and you can have it done in this way: Taking the previous example further, if besides first and last names cell A2 also contains a middle name, how do you extract it? How to get substring between 2 delimiters Finally, the TRIM function removes extra spaces, and you get the following result: Instead of LEN, you can put a number that represents the longest surname you expect to find, for example 100. So, in the num_chars argument, you simply supply the total length of the original string returned by the LEN function. There is no need for us to calculate the end position exactly (as you remember, if start_num and num_chars combined is bigger than the total string length, all remaining characters are returned). To extract the last name from A2, use this formula:Īgain, you use the SEARCH function to determine the starting position (a space). And then, you use the MID function to return a substring beginning with the fist character and up to the character preceding the space, thus fetching the first name. The SEARCH function is used to scan the original string for the space character (" ") and return its position, from which you subtract 1 to avoid trailing spaces. MID formula to get the first nameĪssuming the full name is in cell A2, first and last names separated with a space character, you can pull the first name using this formula: But as is often the case in Excel, the same thing can be done in a variety of ways. If you've had a chance to read our recent tutorials, you already know how to pull the first name using the LEFT function and get the last name with the RIGHT function. When dealing with real-life tasks in Excel, you will most often need to use MID in combination with other functions as demonstrated in the following examples.
0 Comments
Leave a Reply. |