Excel Split Up Cell Contents Full Name Into

The most common example for this is to split first name and last name.

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.

Excel Split Up Cell Contents Full Name Into

So let us take an example of splitting full name into first, middle and last names.

Syntax of Left function:LEFT(text,numchars) Where Text is the text string that contains the characters you want to extract.

Numchar Specifies the number of characters you want LEFT to extract.

Note: 1.Numchars must be greater than or equal to zero.2.If numchars is greater than the length of text, LEFT returns all of text.3.If numchars is omitted, it is assumed to be 1.

Syntax of Find Function: FIND(findtext,withintext,startnum) Where Findtext is the text you want to find.

Startnum specifies the character at which to start the search.

Syntax of Right function: RIGHT(text,numchars) Where Text:is the text string containing the characters you want to extract.

Numchars: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.

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,oldtext,newtext,instancenum) Where Text: is the text or the reference to a cell containing text for which you want to substitute characters.

Instancenum: specifies which occurrence of oldtext you want to replace with newtext.

Otherwise, every occurrence of oldtext in text is changed to newtext.

If there is no middle name, the excel function will return Value.

Excel For Mac Split Up Cell Contents