Print this page

How do I locate all characters to the LEFT or RIGHT of a specified character in a text field using a formula

Knowledge Article Number 000221210
Description

The LEFT function returns the specified number of characters from the beginning of a text string.
The RIGHT function returns the specified number of characters from the end of a text string.

How do I locate all characters to the LEFT or RIGHT of a specified character in a field.
Example: 'Email Address' field: john.smith@company.com

How do I extract john.smith OR company.com using '@' as the specified character?

Resolution


Using Example: 'Email Address' field: johnny.smith@company.com

To extract 'johnny.smith' from the field above, we would use the LEFT function.

LEFT(api name of field, FIND("text", api name of field)-1)

The "text" in the 'FIND(text, api name of field)-1' is the specified character in which you are looking for.
If you do not use '-1', the results will include the specified character. If you use '-1' at the end of the formula, it will extract every character to the left of the specified character.

Here is what it should look like to capture the LEFT of a specified character.

'Email Address field': johnny.smith@company.com
Email Address field API name: email_address__c
Formula Field Expression: LEFT(email_address__c, FIND("@", email_address__c)-1)
Results: johnny.smith
----------------------------------------------

Using Example: 'Email Address' field: johnny.smith@company.com

To extract 'company.com' from the field above, we would use the RIGHT function.

The RIGHT function does not behave like the LEFT function. The RIGHT function returns the specified number of characters from the end of a text string. When combining the RIGHT function with the FIND function, the system will first search from the left for the specified character. Then depending on the location(# of characters from the left), it will then count from the right and extract the same # of characters that it took to 'FIND' the specified character.

'Email Address' field: john.smith@company.com
Email Address field API name: email_address__c

Formula Field Expression: RIGHT(email_address__c, FIND("@", email_address__c))
Results: h@company.com

Notice that you have the 'h@' but your desired results should be 'company.com'. This is because the '@' is the 13th character(from the left) in the text string. However, 'company.com' is 11 characters from the end of the text string. 

Workaround- Use the LEN(length) function:
RIGHT(api name of field,(LEN( api name of field)-FIND("text", api name of field)))

This formula will search for the location of '@' in the text string. Then it will determine the total Length of the text string and subtract the location of '@' in the text string. This will leave the remaining characters which is equivalent to the RIGHT of what you're specifying.

Example:
'Email Address' field: john.smith@company.com
Email Address field API name: email_address__c

Formula Field Expression: RIGHT( email_address__c,(LEN( email_address__c)-FIND("@", email_address__c)))
Results: company.com





promote demote