To select the first 5 characters from another cell in Microsoft Excel 2010/2013, we need to use the “Left” function.
LEFT: Returns the specified number of characters starting from the left-most character in the text string.
Syntax of “LEFT” function: =LEFT (text,[num_chars])
Example:Cell A2 containsa name, to separate the first name, we will use =LEFT(A2, 4),
Thus, the function will return “Bush”.
Let’s take an example to understand how to select the first 5 character from another cell.
Example 1: I have a list of email addresses in column A. The username is the first 5 characters of the email addresses.
To separate the first 5 character from the list follow the below mentioned steps:-
- Select the Cell B2, write the formula =LEFT (A2, 5) and press Enter on the keyboard.
- The function will return the first 5 characters from the cell.
- To Copy the formula in all cells select cell B2, press the keys“CTRL + C”on the keyboard and select the rangeB3 to B6 and press key “CTRL + V”.
Note: If there is no specific number of characters for the name in the email id,you can use the “Left” function along with the “Search” function.
SEARCH:The SEARCH function return the starting position of a text string which it locates from within the specified text string.
Syntax of “SEARCH” function: =SEARCH (find_text,within_text,[start_num])
Example:Cell A1containsthe text “Bush, George”
=SEARCH (“,”, A1, 1), function will return5
This means that the “comma” is located at the 5 position in the text string, starting from the left-most character.
To separate the name from the list by using the “Left” function along with “Search” function follow the below mentioned steps:-
- Select the Cell B2, write the formula=LEFT (A2, SEARCH (“@”, A2)-1). Press Enter on the keyboard.
- The function will return the name from the email id.
- To Copy the formula in all cells press key “CTRL + C”on the keyboard and select the range B3 to B6 and press key “CTRL + V”.
Here we can see that the name is returned from the email id irrespective of how many characters are present in the name.