Using formulas to replace the beginning of a text field

[Tina_Bassir]

I’m struggling to get the desired results using the Substitute formula. Maybe I need to try the Replace formula. But thought I’d ask here in the meantime.

I have a list of tasks. Each task starts with a client code and project code. How can I write a formula to update the client code and project code for each new client/project?

For example, if the one task is “CLI-PRJ-Send thank you to client.”
I would like “CLI” replaced with “4CR”
I would like “PRJ” replaced with “Blog”
So the new task would be “4CR-Blog-Send thank you to client.”

Thanks in advance for help on the topic!

[AlliAlosa]

There are several ways to go about this, and finding the best/most direct way depends on how your base set is up. Can you provide a little more info? i.e. how many tables are at play? How is the original task name generated (manual input or formula)?

[Tina_Bassir]

Thanks. There are three tables in the base but the tasks that need to be updated are all in one table. The task name is manually input. It is copied from a template and pasted into the task table. (this is why it comes in as a generic client/project and needs to be edited with the actual client/project)

[AlliAlosa]

Thanks for the additional info! I think this should work for you :slightly_smiling_face:

SUBSTITUTE(SUBSTITUTE(Task, LEFT(Task, FIND("-", Task) - 1), {Client Code}), MID(Task, FIND("-", Task) + 1, FIND("-", Task, FIND("-", Task) + 1) - FIND("-", Task) - 1), {Project Code})

[Tina_Bassir]

Thank you! I’ll give it a try.