In the above formula, FIND(” “,TRIM(A2))) would return 6 as the space character occurs at the sixth position in the name in cell A2.

Let me quickly explain how this formula works.įirst, I have used the FIND function to get the position of the space character in the cell. The above formula will remove everything to the left of the space character (including the space character), and you will get the rest of the text (last name in this example). In most cases, you’re unlikely to get consistent data where the number of characters you want to remove from the left would be of fixed length.įor example, below I have the names dataset where I want to remove the first name and only get the last name. Removing Characters from the Left based on Delimiter (Space, Comma, Dash) If, in the above example, we have inconsistent data where there are varying numbers of characters before the number, we would not be able to use the above formula (use the formula next section in such a scenario). Since we have hardcoded the number of characters we want to remove from the left, this method would only work when you always want to remove the fixed number of characters from the left. This value is then used within the RIGHT function to extract everything except the first three characters from the left. The above formula uses the LEN function to get the total number of characters in the cell in column A.įrom the value that we get from the LEN function, we subtract 3, as we only want to extract the numbers and want to remove the first three characters from the left of the string in each cell.

Microsoft excel for mac flash fill