TRIM formula with character length [closed]

Question

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.

Answer 1

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) 

Answer 2

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.