Skip to main content

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!

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)?

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)?


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)

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)


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



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})

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



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})


Thank you! I’ll give it a try.

Reply


Terms & Conditions