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
Solved
Remove part of a string
+18Try:
LEFT(
Name,
FIND(
" | ",
Name
) - 1
)
+17Do 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?
1 person likes this
kuovonne wrote:
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.
TheTimeSavingCo wrote:
Try:
LEFT(
Name,
FIND(
" | ",
Name
) - 1
)
I have already tested this formula but the formula only gives me an ERROR.
user2100 wrote:
Unfortunately I only get an ERROR with the formula.
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.
+18user2100 wrote:
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
)
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.