Extract the first, middle and last name in Excel 2010

To extract the first, middle and last name we use the formulas “LEFT”, “RIGHT”, “MID”, “LEN”, and “SEARCH” in Excel.

**LEFT:** Returns the first character(s) in a text string based on the number of characters specified.

**Syntax of “LEFT” function: =LEFT (text,[num_chars])**

**Example:**Cell A2 contains the text **“Mahesh Kumar Gupta” **

**=LEFT** (A2, 6), function will return** “Mahesh”**

**RIGHT:**Return the last character(s) in a text string based on the number of characters specified.

**Syntax of “RIGHT” function: =RIGHT (text, [num_chars])**

**Example:**Cell A2 contains the text **“Mahesh Kumar Gupta”**

=**RIGHT** (A2, 5), function will return **“GUPTA”**

**MID:**Return a specific number of character(s) from a text string, starting at the position specified based on the number of characters specified.

**Syntax of “MID” function: =MID (text,start_num,num_chars)**

**Example:**Cell A2 contains the text **“Mahesh Kumar Gupta”**

=**MID** (A2, 8, 5), function will return **“KUMAR”**

**LEN:**Returns the number of characters in a text string.

**Syntax of “LEN” function: =LEN (text)**

**Example:**Cell A2 contains the text **“Mahesh Kumar Gupta”**

=**LEN** (A2), function will return **18**

**SEARCH:**The SEARCH function returns the starting position of a text string which it locates from within the text string.

**Syntax of “SEARCH” function: =SEARCH (find_text,within_text,[start_num])**

**Example:**Cell A2 contains the text **“Mahesh Kumar Gupta”**

=**SEARCH** (“Kumar”, A2, 1), function will return **8**

**How to separating the names in Excel through formula? **

To understand how you can extract the first, middle and last name from the text follow the below mentioned steps:

Example 1: We have a list of Names in Column “A” and we need to pick the First name from the list. We will write the **“LEFT”** function along with the **“SEARCH”** function.

- Select the Cell B2, write the formula =
function will return the first name from the cell A2.*LEFT (A2, SEARCH (” “, A2)),* - To Copy the formula in all cells press the key
**“CTRL + C”**and select the cell**B3 to B6**and press the key**“CTRL + V”**on your keyboard.

To pick the Middle name from the list. We write the **“MID”** function along with the **“SEARCH”** function.

- Select the Cell C2, write the formula
it will return the middle name from the cell A2.**=MID(A2,SEARCH(” “,A2,1)+1,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)-SEARCH(” “,A2,1))** - To Copy the formula in all cells press key “CTRL + C” and select the cell C3 to C6 and press key “CTRL + V”on your keyboard.

To pick the Last name from the list. We we will use the **“RIGHT”** function along with the **“SEARCH”** and **“LEN”** function.

- Select the Cell D2, write the formula
It will return the last name from the cell A2.**=RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,SEARCH(” “,A2)+1)))** - To Copy the formula in all cells press key
**“CTRL + C”**and select the cell**D3 to D6**and press key**“CTRL + V”**on your keyboard.

This is the way we can split names through formula in Microsoft Excel.

To know more find the below examples:-

How to split a full address into 3 or more separate columns

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.

How does one separate out JR’s? eg. John W. Smith Jr.

“Try this to find ‘Smith Jr.’

=IF(ISERROR(SEARCH(“” “”,A2,SEARCH(“” “”,A2)+1)),RIGHT(A2,LEN(A2)-SEARCH(“” “”,A2)),RIGHT(A2,LEN(A2)-SEARCH(“” “”,A2,SEARCH(“” “”,A2)+1)))”

In order to extract the last name you may try a shorter formula.

=TRIM(RIGHT(SUBSTITUTE(TRIM(A2),” “,REPT(” “,255)),255))

Michael (Micky) Avidan

“Microsoft® Answers” – Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

This is awesome…thank you so much…it compensated for errors from folks with only a first and last name (i.e., not middle name).

Thankyou……

Any easiest formula for MID name?

By the Way – if I’m not mistaken the same can be accomplished with the feature: “Text to Columns”.

Michael (Micky) Avidan

“Microsoft® Answers” – Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

plz tell me how to split first and last name if they are combined with any character other than letters. E.G.Avnesh.chaudhary

vishal1mittal

akash#sharma

Prateek_bansal

plz help me..plz

To extract the last name you can use below mentioned formula

=IF (ISNUMBER (FIND (“,”,A2)),RIGHT(A2,LEN(A2)-FIND(“,”,A2)-1),A2)

To extract the first name use this formula =LEFT (A2, SEARCH (“@”, A2)-1).

hi can any 1 provide excel links where i can learn with examples please i will be very thankful to you

To extract the name from right you can use below mentioned formula

=IF (ISNUMBER (FIND (“,”,A2)),RIGHT(A2,LEN(A2)-FIND(“,”,A2)-1),A2)

For Example:-

Column A Column B

Names_______________First Names

Bush, George________George

Seinfeld, Jerry_____Jerry

Jordan, Michael_____Michael

Laura_______________Laura

Kate________________Kate

To extract the name from left use this formula =LEFT (A2, SEARCH (“@”, A2)-1)

For Example:-

Column A Column B

Names_______________First Names

type1@gmail.com________type1

type12@gmail.com_____type12

type123@gmail.com_____type123

type1234@gmail.com_____type1234

@Nisha Dahawan,

To the best of my knowledge your, suggested, formula does not extract the Last(!) name.

Please recheck.

Michael (Micky) Avidan

“Microsoft® Answers” – Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

HI please help

i need destination 3 charac in between below mentioned city routing,

YWGYYZLGAYYZYWG

YYZPHLMANPHLYYZ

YYZLHRCAI

For getting last name : use

=MID(A2,LOOKUP(1,–((MID(A2,ROW(INDIRECT(“1:” &LEN(A2))),1))=” “),ROW(INDIRECT(“1:” &LEN(A2)))),50)

Use Flash Fill, new in Excel 2013, to fill out data based on an example. Flash Fill typically starts working when it recognizes a pattern in your data, and works best when your data has some consistency.

excel 1st sheet a,b.given 2 sheet full name given but 1 full name

excel 1st sheet a,b.c given 2 sheet full name given but 1 full name then 1 st sheet b number come full name

I have a problem extracting compound given names and single names, as well as their middle initial in this order

Larry, Martin Luther S.

Hudson, Mary L.

I used the formula =LEFT(MID(A6,FIND(” “,A6)+1,LEN(A6)),FIND(” “,MID(A6,FIND(” “,A6)+1,LEN(A6)))-1) for first name. OUTPUT was Martin only, I need to add Luther but for Hudson, Mary works just fine,

In the middle initial I used the formula =RIGHT(A23,LEN(A23)-FIND(” “,A23,FIND(” “,A23,FIND(” “,A23)+2))), for Larry, Martin Luther S, output sa Luther S. how can i eliminate Luther but for Hudson, Mary L works just fine,, what should I do? thank you,

HELP please: GrahamMaurice – how to obtain two columns = Maurice and the other = Surname = Graham?

Thinking this is simple excel; but I am simple!; best Tony Clemenger;

and I have thousands of them:

CiciullaSerge

CullumCoral

HookeyChad

RissonGarry

MelvilleScott

WilliamsRodney

MunroFiona

CraftJamie

BergincPeter

RowlandsScott

KoumidesMel

RavenMark

McGurganJustin

SymonsRycki

KellerEdward

CAN HELP???

HI Tony,

Here is a formula which can help you split First name and Last name wherein the ONLY delimiter is an Uppercase. This should work on major of the names given in your example list apart from the name like “McGurganJustin” which has 3 uppercase.

Enter the following formula in the designated cells; Considering A1 contains the name: CiciullaSerge

C1 (Last Name) : =MID(A1,MATCH(1,(CODE(MID(A1,ROW($1:$255),1))>=65)*(CODE(MID(A1,ROW($2:$255),1))<90),)+1,255)

PLEASE NOTE: This is an array formula (press CTRL+SHIFT+ENTER)

B1 (First Name) : =SUBSTITUTE(A1,C1,"")

Please help. I need to retrieve the middle name with the first character and the last character being in upper case. for instance retrieve “BillY” from broncho billy anderson

Hi Brian,

We are glad to assist you. Also, we would request you if you have any simple or complicated query, please visit Excel Forum and ask your query to our expert. You will get the solution to your queries in very less time.

Coming back to your query asked by you above, we assume you have “broncho billy anderson” in cell A2. Enter the following formula in B2 to get the desired result as “BillY”.

=PROPER(MID(A2,SEARCH(” “,A2,1)+1,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)-SEARCH(” “,A2,1)-2))&””&PROPER(MID(A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)-1,1))

Let us know if it helps you to meet your requirement.

If you like the solution, we request you to visit our Facebook page and like us.

Best Regards,

Team Excel Tip & Excel Forum

how i can make that Name As M k Gupta

Hi Manoj,

We would request you to please provide the exact criteria to meet your expected result. So our experts can help you in achieving the same.

Thanks in advance!

Regards,

Team Excel Tip & Excel Forum

Just don’t get in all this mess just simply copy paste the formula given below in formula bar:

=LEFT(A2, SEARCH (” “, A2)-1)

=IFERROR(MID(A2,SEARCH(” “,A2,1)+1,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)-SEARCH(” “,A2,1)),””)

=IFERROR(REPLACE(A2,1,SEARCH(“^”,SUBSTITUTE(A2,” “,”^”,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)))),””),””)

Recommended only on 2010 and above

None of these work………you either get a Value! or Name! error message……………….

Hi,

this formulae is used for three names.

i have a requirements of 4 names. how to split them in 4 columns?

for example, i have First Name, Second Name, Third Name and Forth Name in one column combined all together. i want to split them in four columns.

Regards.

Hi Muhammad,

Assuming you have “First Second Third Fourth” in cell A1 and as per your request you want to split them as First, Second, Third & Fourth names in individual columns respectively so the formula would be..

To extract

First Name –> “=LEFT(A1,SEARCH(” “,A1,1)-1)”

Second Name –> “=MID(A1,SEARCH(” “,A1,1)+1,(SEARCH(” “,A1,SEARCH(” “,A1,1)+1)-1)-SEARCH(” “,A1,1))”

Third Name —> “=MID(A1,SEARCH(” “,A1,SEARCH(” “,A1,1)+1)+1,SEARCH(” “,A1,SEARCH(” “,A1,SEARCH(” “,A1,1)+1)+1)-SEARCH(” “,A1,SEARCH(” “,A1,1)+1)-1)”

Fourth Name —> =RIGHT(A1,LEN(A1)-SEARCH(” “,A1,SEARCH(” “,A1,SEARCH(” “,A1,1)+1)+1))

Hope this is what you were looking for.

Also, for any simple or complicated query, please login on http://www.excelforum.com and ask our experts.

Happy Learning,

Site Admin

Hi.. I need to extract the first and last name from an email address. please help

e.g

wendy.poulton@eskom.co.za

Hi Samson,

We assume that you have all emails like this.

To extract left name “wendy”, use the following formula –> “=LEFT(“wendy.poulton@eskom.co.za”,SEARCH(“.”,”wendy.poulton@eskom.co.za”,1)-1)”

To extract the last name “poulton”, use the following formula –> “=MID(“wendy.poulton@eskom.co.za”,SEARCH(“.”,”wendy.poulton@eskom.co.za”,1)+1,SEARCH(“@”,”wendy.poulton@eskom.co.za”,1)-SEARCH(“.”,”wendy.poulton@eskom.co.za”,1)-1)”.

You may find both formulas lengthy but you can replace the email id with the cell reference and it will extract the first and last name from email address.

Also, we will request you to please visit http://www.excelforum.com in case you have any Excel/VBA query. You can ask our experts and get the instant solutions for your queries.

Thanks,

Site Admin

Hi All, i have a list of names Ex-

CA

PA-US-PA

PA-US-PA

PA-US-PA

IN

PA

however, my friend is asking me to put one formula for all but the outcome should only be “US”. in this case single words will remain same like CN,IN and for 3 words (PA-US-PA) the result should be only US.

Pls help me with this i m nt able to put a logic here.

Hi Ragini,

As per requirement, you don’t want to extract the names however you need to use the logical statement here which will check if single word, then return as it is otherwise look for “-US*” and return “US” as output in the cell. If this is the case, following is the formula will help you to retrieve expected output.

=IF(COUNTIF(A1,”*-US-*”)=1,”US”,A1)

We assumed you have data in column A from cell A1 to A6. Put the above formula in corresponding cell (B1 to down) and it will return what you looking for.

Also, we’d recommend you to login on our official website for your simple or complicated Excel/VBA query and get instant solution for the same.

Cheers!

Site Admin

How about you have 2nd name

ex: “CHLOE RAYNE” MENDOZA DE LEON

John Andrew S. Curry

Hi i need to extract number from below data

‘as12asd21mis45asdds2′ Result 1221452

’11111asdgsadhga1225′ Result 111111225

asafas1457 Result 1457

how to remove space and country code from below data

145 2145145 result 1452145145

9987 954748 result 9987954748

9945 45 4878 result 9945454878

’91 9987 125445 result 9987125445