-4

I need to create a custom formula field where I can get the 6th to 8th character from another text field. Say I have a custom field (text) Customer_c whose value is ABCDEFGHIJKL. I need to create formula field where I can display FGH (i.e 6th to 8th character) of the text. Please help me with the formula.

I have tried this formula which is saving but it is not displaying any data in the formula field.

TRIM(
    LEFT(
        TRIM(
            RIGHT(
                Customer_c,  
                (LEN(Customer_c) - 6) 
            ) 
        ),
        (LEN(Customer_c) - 8)
    )
) 

I only need to calculate the position of the characters from LEFT.

7
  • Do you want the 10-15th or the 6-8th? What if the strings are not that long?
    – Eric
    Apr 24, 2017 at 14:22
  • 1
    What have you tried so far? Have you taken a look at the formula functions (specifically the text functions)? -1 from me for lack of research.
    – Derek F
    Apr 24, 2017 at 15:04
  • I just have an exsmple as how to find the trimmed string. Please help me with 6-8th string. Apr 24, 2017 at 15:27
  • 1
    That would be something to add to your question (rather than keep in the comments). Also, "It didn't work" isn't a helpful statement. If you're getting an error, you should include the text of the message verbatim. If you aren't getting an error, provide details about the result that you're getting, and how it's different from the result that you're expecting. All that said, you probably want to use MID() instead of RIGHT().
    – Derek F
    Apr 24, 2017 at 15:40
  • 1
    Again, this formula is something that you should include in your question, not the comments
    – Derek F
    Apr 24, 2017 at 16:02

2 Answers 2

3

Based on your description, you want to be using the MID() function, not LEFT(), nor RIGHT().

Documentation is your friend. If you were to look through the documentation, this is the page where you would find information about mid.

From that page:

MID

Description: Returns the specified number of characters from the middle of a text string given the starting position.

Use: MID(text, start_num, num_chars) and replace text with the field or expression to use when returning characters; replace start_num with the number of characters from the left to use as a starting position; replace num_chars with the total number of characters to return.

Example: MID(Division, 3, 4) returns four characters of the Division name beginning with the third character from the left. On a user record, this represents the department code.

From that, we see that the function doesn't take the first and last indices of the characters you want to extract, but rather the first index, and the total number of characters to extract.

Thus, extracting characters 6-8 in a string would look like this:

MID(MyStringField__c, 6, 3)

We start at index 6 (this function uses 1-indexed positions, so the 6th character is index 6), and want the 6th, 7th, and 8th characters (a total of 3).

+edit:

As a bonus, the bit in the documentation about MID() returning the characters from the middle of the string is a bit misleading. There's nothing stopping you from using it to return characters from any location within the string.

You could even use MID() to return the same characters as LEFT() or RIGHT() do (however, using MID() like this would be prone to issues, and it would be safer and easier to just use LEFT() or RIGHT()).

//Equivalent to LEFT(string, number_of_chars)
MID(string, 1, number_of_chars)

// Equivalent to RIGHT(string, number_of_chars)
MID(string, LENTH(string) - number_of_chars + 1, number_of_chars) 
0
1

Simply use MID function like this:

MID(Customer_c,6,3)

Refer Mid function

MID(text, start_num, num_chars) and replace text with the field or expression to use when returning characters; replace start_num with the number of characters from the left to use as a starting position; replace num_chars with the total number of characters to return.

0

Not the answer you're looking for? Browse other questions tagged .