Deleting text after some common ending words

[Jasdev]

Hi,

I have a particular requirement (I can't modify it with something else as other part of my task is assigned like that).

I have a column which has more than 10K records. Many of the records in them have some data after some common endings. I would like to strip of the data after those common ending words. I am pasting a sample data here along with desired output that I want. I have tried multiple posts but can't really figure this out.

In the below sample, Street 1 is that column where I need to place the logic. It has some records ending with some common words such as ave and home (for example in my data). I have a list of such words (around 6-7) with me. Whenever I see those words, I keep anything that is on right of them. Can you please advise me how to do this?

street 1	stree 2	zip
107 spalding trail ABC	123	123
107 spalding ave 123	234	234
107 spalding home Abc	345	345
107 remona trail 1	123	123
107 remona av 2	234	234
107 remona ave 3	345	345
DESIRED OUTPUT BELOW
street 1	stree 2	zip
107 spalding trail ABC	123	123
107 spalding ave	234	234
107 spalding home	345	345
107 remona trail 1	123	123
107 remona av 2	234	234
107 remona ave	345	345

[wdavis]

Hi @Jasdev 

I have mocked up a workflow below which looks for a particular statement, in your case "ave" then removes the last word from that string.

I have made a couple of assumptions within this workflow which might need to be changed however. 

1. Is it always one word/group of numbers that need to be removed?

2. Does the data need to be removed from Field 1, or added in somewhere else?

Thanks

Will

[Jasdev]

Thank you Will. This looks like a start but it needs some more modifications I guess. For your first assumption it works fine as it removes one word "123" from it but if I add "123 Jasdev", it keeps 123 so I am assuming it is removing the last word. I basically have to strip off everything after AVE. For the second assumption, I think that can be taken care of later on. I eventually has to delete it so if it creates a new column, I will just remove that.

Also, One question based on this workflow I have is, as you created an IF formula assuming it to be one word AVE, if i have few more words like these going forward, do i need to write IF for all of them separately? Can't it pick from any file like in the formula find and replace?

[wdavis · Accepted solution]

Hi @Jasdev 

Yes, if that is the case then the updated workflow should hopefully take those into account. I have used a 2nd text input to match where you will have a list of the values to replace, as well as a 'replace' value for them which includes a '|' character.

This then allows us to use a Text to Columns Tool after the Find and Replace to split the data into 2 fields, everything before and everything after the '|'.

The select tool then removes the fields you don't require and renames them.

Let me know if this makes sense and works for your problem.

Thanks

Will

[Jasdev]

Thanks Will..it helps in my scenario and works like a charm!

Regards,

Jasdev