And then RIGHT returns 17 characters from the end (right) of A1.LEN(A1)-(FIND("+",A1)-1) checks the total number of characters in A1 ( 40) and subtracts 23 (counted by FIND) from it: 17.FIND("+",A1)-1 locates the position number of the plus sign in A1 ( 24) and subtracts 1 so the total doesn't include the plus itself: 23.Using this trio in a particular order will help me get the same result and remove the entire text before a character - a plus sign: If they're not, just use the REGEXREPLACE instead or, even better, the easier tool described at the end. These functions will help only if the records to keep are of the same length, like phone numbers in my case. There are a few more Google Sheets functions that let you remove the text before a certain character. and a group of characters between them: (.*)Īnd replaces them with "nothing" (meaning.And you have all the means remove to not just everything in-between but also the brackets themselves. Whenever it's irrelevant, there's no point in keeping it in cells. Only this time, you tell the function to group (and return) everything before the line break and clear out the rest:Īnother case I'd like to mention here concerns the extra info in brackets. In a similar fashion, you can delete all phone numbers yet keep the addresses: As an alternative, you can simply remove the first line if that'd be easier for your task: You should mention those groups in the third argument this way so the formula could return everything that possibly stands before and after the USĪs for the US itself, I simply don't mention it in the 3rd argument - meaning, I want to return everything from A1 without the US. $1 and $2 each represent one of those 2 groups of characters - (.*) - from the previous argument.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |