How to Select First 5 Characters from another Cell in Excel 2010

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),

img1

 

img2

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.

img3

 

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”.

img4

 

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.

img5

 

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

img6

 

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”.

img7

 

Here we can see that the name is returned from the email id irrespective of how many characters are present in the name.

Users are saying about us...

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube