Deleting text after some common ending words

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

Preparation

Accepted answers

wdavis

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

TextDeleting.yxmd

All comments

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

TextDeleting.yxmd

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?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703