Skip to main content

Using formulas to replace the beginning of a text field

  • August 22, 2019
  • 4 replies
  • 23 views
T

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!

4 replies

A
Forum|alt.badge.img+19
  • AlliAlosa
  • Inspiring
  • August 22, 2019

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

T
  • Tina_Bassir
  • Author
  • New Participant
  • August 22, 2019

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)

A
Forum|alt.badge.img+19
  • AlliAlosa
  • Inspiring
  • August 22, 2019

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

T
  • Tina_Bassir
  • Author
  • New Participant
  • August 23, 2019

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


Thank you! I’ll give it a try.

Terms & ConditionsAccessibility statement