I have an issue I am trying to resolve where I only want to keep the text right of a certain string in a cell however the particular string occurs twice in the cell in the Excel file. I am currently using the below formula to try to get it to work but it is not working correctly. I cannot use a find/replace in the parse tool because the string is not always the same. Shown below is the input have along with the desired output.
Current Formula: Right([Replaced ERR_TXT], FindString([Replaced ERR_TXT], '<Messages><Messages>'))
Input | Desired Output |
<Messages> <Message> Desired Remaining Text <Messages> <Message> | Desired Remaining Text |
Long sting of varying text which may change from row to row and definitely day to day <Messages> <Message> Desired Remaining Text <Messages> <Message> | Desired Remaining Text |
Solved! Go to Solution.
@MattR79
well that probably not the best solution however what you can do is, parse the text using Text to Columns, selecting the Split to rows. selecting space as the delimiter. Then put a RecordID. After a Summarize Tool selecting Min RecordID group on the text. Now you have the first text range. filter the text based on the highest number from the Min results, all the RecordID that equals or smaller than it and connect it to a Summarize Tool and Concatenate the data with a space as a delimiter. Now you should have the text without the duplicated message.
It is not the nest solution, however it is one that you can use.
@MattR79 I will advise to use the regex tool and follow below steps -
Mention the column to parse
In regular expression mention this expression .*>(.*)<.*><.*
Output method - Parse and you will get the string in a new column whatever you name it
Hope this helps