To select the first 5 characters from another cell in Microsoft Excel 2016/2013/2010, 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:
Example: In this example Cell A2 contains a code whose first 5 characters are city code, to separate the city code from the text, we should know excel formula to copy the first 5 characters in excel:
Thus, Excel matches first 5 characters and returns “11005”, as shown in the image. Copy the formula in the cells below to do the same for other codes.
This formula is the command that locates and selects specific text.
Let’s take an example to understand how to select the first 5 characters 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 characters from the list, follow the below mentioned steps:-
Select 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 range B3 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 within the specified text string.
Syntax of “SEARCH” function:
Use FIND instead of SEARCH if you want it to be case sensitive.
Cell A1 contains the text “Bush, George”
=SEARCH (“,”, A1, 1), the function will return 5.
This means that the “comma” is located at the 5 positions 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. Press Enter on the keyboard.
|=LEFT (A2, SEARCH (“@”, A2)-1).|
The function will return the name from the email id.
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.
Sometimes you gotta retrieve text from right. As in above image, you can see we have column name Full Name. It has name in reverse order (surname_firstname). Next to name column we have a column named Get First Name. We need to extract first name from full name in Column B. Lets see the formula first:
Copy this formula in B2 and drag it down.
Looks complicated? Its not. Let me explain. As in all formulas, middle function is translated first (except IF), we will start from middle too.
LEN(A2): The LEN function in excel counts how many characters in a cell. It will translate to 12. Which makes our function
FIND(“_”,A2): Similar to SEARCH function, FIND function returns the position of character/s in cell. This will translate to 6. Which makes our function
|=RIGHT(A2,12-6) ? =RIGHT(A2,6)|
RIGHT(A2,6): The RIGHT function in excel returns specified number of characters from a cell. Here it will translate to Manish.
I tried to explain use of LEFT and RIGHT functions and how you can combine them with LEN, SEARCH and FIND function to make it dynamic. I hope it was helpful, but if it wasn’t let me know it in the comments section.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.