Sep 09, 2024 05:41 AM
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! Go to Solution.
Sep 09, 2024 06:16 AM
Try:
LEFT(
Name,
FIND(
" | ",
Name
) - 1
)
Sep 09, 2024 09:36 AM
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?
Sep 09, 2024 06:16 AM
Sep 09, 2024 09:36 AM
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?
Sep 09, 2024 11:48 PM
Sep 09, 2024 11:49 PM
I have already tested this formula but the formula only gives me an ERROR.
Sep 09, 2024 11:54 PM
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.
Sep 10, 2024 02:48 AM - edited Sep 10, 2024 02:48 AM
Glad you figured it out! For a lookup field you can try the following too:
LEFT(
Name & "",
FIND(
" | ",
Name & ""
) - 1
)