We can utilize various functions or a combination of them to extract specific strings in Excel. Here are some key substring functions available in Excel:

1. LEFT
2. RIGHT
3. MID
4. LEN
5. FIND
6. TRIM
7. TEXTBEFORE
8. TEXTAFTER

Let's explore each of these functions in detail. You can also download the Excel Workbook to practice along.

Download excel workbookSubstring-in-Excel.xlsx

LEFT

The LEFT function is used to extract a specified number of characters from the beginning of a text string.

LEFT(text, [num_chars])

• text – This is the text string from which you want to extract characters.
• num_chars – Optional. It specifies the number of characters to extract from the left.
  • Num_chars must be at least zero.
  • If num_chars exceeds the text length, LEFT returns the entire text.
  • If num_chars is not provided, it defaults to 1.

For example, to extract the first three characters from the text in cell A2:

STEP 1: Enter the LEFT function in the cell where you want to display the result:

=LEFT(

STEP 2: Add the first argument – text. This is the cell reference containing the text from which you want to extract characters.

=LEFT(A2

STEP 3: Add the second argument – [num_chars]. Specify the number of characters to extract from cell A2. Use a positive number.

=LEFT(A2, 3)

RIGHT

The RIGHT function is used to extract a specified number of characters from the end of a text string.

RIGHT(text,[num_chars])

• text – This is the text string from which you want to extract characters.
• num_chars – Optional. It specifies the number of characters to extract from the right.
  • Num_chars must be at least zero.
  • If num_chars exceeds the text length, RIGHT returns the entire text.
  • If num_chars is not provided, it defaults to 1.

For example, to extract the last four characters from the text in cell A2:

STEP 1: Enter the RIGHT function in the cell where you want to display the result:

=RIGHT(

STEP 2: Add the first argument – text. This is the cell reference containing the text from which you want to extract characters.

=RIGHT(A2,

STEP 3: Add the second argument – [num_chars]. Specify the number of characters to extract from cell A2. Use a positive number.

=RIGHT(A2, 4)

MID

The MID function is used to extract a specified number of characters starting from a specific position within a text string.

MID(text,start_num,num_chars)

• text – This is the text string from which you want to extract characters.
• start_num – This is the position of the first character to extract, starting from 1.
• num_chars – This specifies the number of characters to extract.

For example, to extract four characters starting from the fifth position of the text in cell A2:

STEP 1: Enter the MID function in a blank cell.

=MID(

STEP 2: Add the first argument – text. Select the cell containing the text from which you want to extract.

=MID(A2,

STEP 3: Add the second argument – start_num. Specify the starting position for extraction.

=MID(A2, 5,

STEP 4: Add the third argument – num_chars. Specify the number of characters to extract.

=MID(A2, 5, 4)

LEN

The LEN function is used to return the number of characters in a text string.

LEN(text)

• text – This is the text string from which you want to count the characters.

For example, to count the number of characters in the text in cell A2:

STEP 1: Enter the LEN function in the cell where you want to display the result.

=LEN(

STEP 2: Add the first argument – text. This is the cell reference containing the text.

=LEN(A2)

FIND

The FIND function is used to locate the position of a specific text within another text.

FIND(find_text, within_text, [start_num])

• find_text – This is the text to search for.
• within_text – This is the text in which to search.
• start_num – Optional. This is the starting position for the search.

For example, to find the position of a space (" ") in the text in cell A2:

STEP 1: Enter the FIND function in a blank cell.

=FIND(

STEP 2: Add the first argument – find_text. Enter the text to search for, in this case, a space (" ").

=FIND(" ",

STEP 3: Add the second argument – within_text. Select the cell containing the text to search.

=FIND(" ", A2)

STEP 4: Add the third argument – [start_num]. This is optional and defaults to 1 if omitted.

TRIM

The TRIM function is used to remove unnecessary spaces from text, keeping only single spaces between words.

TRIM(text)

• text – This is the text from which you want to remove extra spaces.

For example, to remove all extra spaces from the text in cell A2:

STEP 1: Enter the TRIM function in the cell where you want to display the result.

=TRIM(

STEP 2: Add the argument – text. This is the cell reference containing the text from which to remove extra spaces.

=TRIM(A2)

TEXTBEFORE

The TEXTBEFORE function is used to extract text that appears before a specified character or string. It is available in Excel 365.

TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

• text – This is the text to search within. Wildcard characters are not allowed.
• delimiter – This is the text that marks the point before which to extract.
• instance_num – Optional. This specifies the instance of the delimiter to use. Default is 1.
• match_mode – Optional. Determines case sensitivity. Default is case-sensitive.
• match_end – Optional. Treats the end of the text as a delimiter. Default is exact match.
• if_not_found – Optional. Specifies the value to return if no match is found. Default is #N/A.

For example, to extract the text before a space in cell A2:

STEP 1: Enter the TEXTBEFORE function in the cell where you want to display the result.

=TEXTBEFORE(

STEP 2: Add the first argument – text. Enter the cell reference containing the text.

=TEXTBEFORE(A2,

STEP 3: Add the second argument – delimiter. This is the character before which to extract, in this case, a space.

=TEXTBEFORE(A2," ")

If you don't have this function, you can use a combination of LEFT and FIND functions to extract the text before a space.

STEP 1: Enter the LEFT function.

=LEFT(

STEP 2: Add the first argument of the LEFT function – text. This is the cell reference containing the text.

=LEFT(A2,

STEP 3: Add the FIND function to find the position of the space.

=LEFT(A2,FIND(

STEP 4: Add the first argument of the FIND function – find_text. This is the character to search for, in this case, a space.

=LEFT(A2,FIND(" ",

STEP 5: Add the second argument of the FIND function – within_text. This is the cell reference containing the text.

=LEFT(A2,FIND(" ",A2)

STEP 6: Subtract 1 to get the number of characters before the space.

=LEFT(A2,FIND(" ",A2)-1)

TEXTAFTER

The TEXTAFTER function is used to extract text that appears after a specified character or string. It is available in Excel 365.

TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

For example, to extract the text after a space in cell A2:

STEP 1: Enter the TEXTAFTER function in the cell where you want to display the result.

=TEXTAFTER(

STEP 2: Add the first argument – text. Enter the cell reference containing the text.

=TEXTAFTER(A2,

STEP 3: Add the second argument – delimiter. This is the character after which to extract, in this case, a space.

=TEXTAFTER(A2," ")

If you don't have this function, you can use a combination of RIGHT, LEN, and FIND functions to extract the text after a space.

STEP 1: Enter the RIGHT function.

=RIGHT(

STEP 2: Add the first argument of the RIGHT function – text. This is the cell reference containing the text.

=RIGHT(A2,

STEP 3: Add the LEN function to find the total number of characters in the text.

=RIGHT(A2,LEN(A2)

STEP 4: Add the subtract symbol.

=RIGHT(A2,LEN(A2)-

STEP 5: Add the FIND function to find the position of the space.

=RIGHT(A2,LEN(A2)-FIND(

STEP 6: Add the first argument of the FIND function – find_text. This is the character to search for, in this case, a space.

=RIGHT(A2,LEN(A2)-FIND(" ",

STEP 7: Add the second argument of the FIND function – within_text. This is the cell reference containing the text.

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

The above is the detailed content of Unlock the Power of Excel: 8 Substring Functions for Easy Data Analysis. For more information, please follow other related articles on the PHP Chinese website!