There are many instances we need to split a text in a cell. The most common example for this is to split first name and last name. We can perform this using text to column feature of Excel. But if you want to create an template in Excel which requries you to split text in a cell, then you need to use either Excel function or Excel VBA.
Luckily, Excel provides handy function through which we can extract desired word from the text.The formula uses various text functions to accomplish the task.Each of the techniques used here based on the ‘space’ between the names to identify where to split. So let us take an example of splitting full name into first, middle and last names.
Example:
Splitting a text in a cell using Excel Function
Extracting only First name from text using Excel Formula
For extracting first name from full name in cell, we can use left and find functions. So here is the way to how to do it.
Enter the formula in cell C2:
=LEFT(B2,FIND(” “,B2,1))
this will return Alan. similarly same formula in B3 will return Bob.
Syntax of Left function:LEFT(text,num_chars)
Where
Text is the text string that contains the characters you want to extract.
Num_char Specifies the number of characters you want LEFT to extract.
Note: 1.Num_chars must be greater than or equal to zero.2.If num_chars is greater than the length of text, LEFT returns all of text.3.If num_chars is omitted, it is assumed to be 1.
Syntax of Find Function: FIND(find_text,within_text,start_num)
Where
Find_text is the text you want to find.
Within_text is the text containing the text you want to find.
Start_num specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.
Extracting the Last Name from Text using Excel Formula
For extracting last name from a cell in excel, we need to take help of 3 functions. RIGHT, LEN and FIND function.
Eg: in cell E2 enter:
=RIGHT(B2,LEN(B2)-FIND(“#”,SUBSTITUTE(B2,” “,”#”,LEN(B2)-LEN(SUBSTITUTE(B2,” “,””)))))
will return Jones. Same formula for the rest the rows.
Syntax of Right function: RIGHT(text,num_chars)
Where Text:is the text string containing the characters you want to extract.
Num_chars:specifies the number of characters you want RIGHT to extract.
Above functions will not be able to handle any more than two names.If there is also a middle name, the last name formula will be incorrect.To solve the problem you have to use a much longer calculation.
Here is the function to solve this.
Enter =RIGHT(A4,LEN(A4)-FIND(“#”,SUBSTITUTE(A4,” “,”#”,LEN(A4)-LEN(SUBSTITUTE(A4,” “,””))))) in cell B4 will return Smith. Here we have used additional function Substitute
Syntax of Substitute function:
SUBSTITUTE(text,old_text,new_text,instance_num)
Where
Text: is the text or the reference to a cell containing text for which you want to substitute characters.
Old_text: is the text you want to replace.
New_text: is the text you want to replace old_text with.
Instance_num: specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.
Excel Function to extract middle name
Enter the following function in the cell E2:
=RIGHT(B2,LEN(B2)-FIND(“#”,SUBSTITUTE(B2,” “,”#”,LEN(B2)-LEN(SUBSTITUTE(B2,” “,””)))))
You can use these functions in your excel sheets by making appropriate changes in the column and row numbers. If there is no middle name, the excel function will return #Value.
Leave a Reply