Replace - replace whole cell value on partial match?

[itoldusoandso]

Is there a way to do Replace transformation but replace the whole cell value on partial match?

For example, in the column Transaction type, there are cell values I want to replace:
Tax on Shipping Big Bag
Tax on Shipping Small Shirt

I want to replace the values with just “Tax on Shipping” and delete what comes after that.

The reason I want to do that is that would enable me to do Spread transformation.

I can’t use Spread transformation because the name after “Tax on Shipping…” varies.

I also tried to replace the values in Transaction type with multiple IF transformations and while it works, it also takes many steps. It would save me a few steps with the IF transformations, if I could set the IF formula to point to the existing field value for all matches or non-matches.

So Spread transformation didn’t work so I ended Concatenating the Transaction type and Amount columns and then using a chain of multiple Split transformations to get the individual values into individual columns I need and then rename the said columns into what I want them to show.

This works but there are many steps involved, in particular had to use the Rename Col step after every Split step otherwise the rename job will be difficult after many Split steps accumulate.

No need to do the workflow for me, just tell me what transformations I need and I’ll try first myself. If I have issues I’ll let you know.

This works, albeit it’s long…

This doesn’t work because some Transaction type fields are infinitively unique. I would need to rename them.

[Anonymous]

Hi,

@itoldusoandso

No need to do the workflow for me, just tell me what transformations I need and I’ll try first myself. If I have issues I’ll let you know.

Since you said you want to do it yourself, I am not uploading the transform file.

Just one request though, in future please provide sample data in text format and not in screenshot, like save the test data in csv format and attach it with your question.

[Admin]

To replace ‘Tax on Shipping Big Bag’ or ‘Tax on Shipping Small Shirt’ with ‘Tax on Shipping’ ypu can use Replace with a regular expression:

[itoldusoandso]

Perfect this helped. I was missing the dot “.” before the asterix sign.

The reason I don’t upload files is because I am remoting to a system and working on an iPhone mini screen, it’s not fun and I don’t want to deal with sending files and uploading downloading. I mostly figure things out. I’ll ask questions if I need more help. Thank you.