Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Remove part of a string

Topic Labels: Data
Solved
Jump to Solution
412 6
cancel
Showing results for 
Search instead for 
Did you mean: 
user2100
6 - Interface Innovator
6 - Interface Innovator

I have a name and the corresponding date of birth in a data field, separated by a "|" I would like to use a formula so that only the name is displayed to me. the name is before the "|" sign. I tried LEFT, but only got ERROR

2 Solutions

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Try:

LEFT(
  Name,
  FIND(
    " | ",
    Name 
  ) - 1
)

Screenshot 2024-09-09 at 9.16.31 PM.png

See Solution in Thread

kuovonne
18 - Pluto
18 - Pluto

Do you have any records where the field has a name, but does not have the "|" character? If so, Adam's formula will not extract the name. There are many ways of doing things in formulas, here is one alternative that will get the name before the "|" or return the full value if there is no "|".

 

TRIM(
  REGEX_EXTRACT({Name}, "[^|]*")
)

 


You might also want to look at why the field is setup as it is. In good database design, each editable field should contain the smallest unit of meaningful data. In this field, you have two different pieces of meaningful data (name and birthdate). 

How is the field with the name and birthdate getting its values? If it is a formula field, is it based on separate fields for the name and birthdate that you can use? If it is a text field with data that is entered manually, can you redesign the base so that the data is entered into two different fields? If it is a text field that is populated via an integration or automation, can you edit the integration or automation?

See Solution in Thread

6 Replies 6
TheTimeSavingCo
18 - Pluto
18 - Pluto

Try:

LEFT(
  Name,
  FIND(
    " | ",
    Name 
  ) - 1
)

Screenshot 2024-09-09 at 9.16.31 PM.png

kuovonne
18 - Pluto
18 - Pluto

Do you have any records where the field has a name, but does not have the "|" character? If so, Adam's formula will not extract the name. There are many ways of doing things in formulas, here is one alternative that will get the name before the "|" or return the full value if there is no "|".

 

TRIM(
  REGEX_EXTRACT({Name}, "[^|]*")
)

 


You might also want to look at why the field is setup as it is. In good database design, each editable field should contain the smallest unit of meaningful data. In this field, you have two different pieces of meaningful data (name and birthdate). 

How is the field with the name and birthdate getting its values? If it is a formula field, is it based on separate fields for the name and birthdate that you can use? If it is a text field with data that is entered manually, can you redesign the base so that the data is entered into two different fields? If it is a text field that is populated via an integration or automation, can you edit the integration or automation?

Unfortunately I only get an ERROR with the formula.

I have already tested this formula but the formula only gives me an ERROR.

I managed it. It was probably because it was a linked field. I tried it with an unlinked field and it worked. thanks for the numerous help.

Glad you figured it out!  For a lookup field you can try the following too:

 

LEFT(
  Name & "",
  FIND(
    " | ",
    Name & "" 
  ) - 1
)