I am trying to clean up a text-to-columns tool, and I am wondering if there is a way to delete or make null a certain amount of rows. For example, if the row says "DATE:" then make that and the next 5 rows null.
Solved! Go to Solution.
It might actually be easiest to do this with a filter tool using a custom filter:
!Contains([data],"Date:" AND !Contains([data],"TIME:" AND !Contains([data],"--------------" AND !Contains([data],"UNIT" AND !Contains([data],"ID."
This worked, thank you!
Hi Brandon,
What if I want to get rid of "Deductible State Tax:" and the following N of states? Is there an easier way to do that without having to type out the custom filter for each N of states? Or is there another tool I can use? I tried using the Sample tool but I need all of the rows before and after the state info.
Thanks!
Jeanette
Hi @jeanvaladez
I normally use a method like this that uses a formula to create a new column with the value 1 when the field contains the starting row, a running total on that counter field, and finally a filter when the running total is greater than or equal to 1 so that the rows passed through will be that row onward. Picture is below and screenshot is attached:
Hi Brandon,
Thank you for your prompt response. However, the picture I attached is what I need to get rid of so how would I do that for all the rows pictures in that column for the states? Sorry for the confusion.
Thanks,
Jeanette
You can just take the results from the F output on the filter and it will show all of the rows that come before the one that have indicated