This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have a column of data that contains a bunch of messy data (Account Numbers). I want to filter on fields that contain numbers and/or letters - they are account numbers generally 7 to 9 digits long. There is other data in the Account Numbers column that have letters that I don't want want. The field is set as a "string" which I am not sure if that is right. They have specific characters (* : -). If it's easier, I can just filter out the data that contains special characters. Is there a way to filter them all at once, or do I need to do separate filters?
The best way to get an answer would be to provide some sample data. In this case, there are a few way to go about removing special characters.Do you only want to remove special character and leave anything alphanumeric, or are account numbers only numeric and you want to remove anything that isn't a numeric character? Use of the RegEx tool will probably be the best bet in any case.
I've attached an example that used the RegEx tool to replace any non-alphanumeric character with nothing.
I think what @BenMoss was referring to wasn't how to trim the whitespace, but using the whitespace to determine which lines to keep. In the sample you gave, it looks like every line with an account number has a number of spaces at the beginning. With a Filter tool, you can add the formula he supplied and get just the records with the leading whitespace, and then trim. Does that make sense? See attached for an example.
Can you give an example of what the output would look like? Are you looking for just the account numbers, or do you want the descriptions that follow as well?