• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Macro to Split First Name Last Name Middle Name Surname

B

Balajisx

Member
Hi All,

I am very new to excel macros. Currently I am in need of a macro which helps me to split the First name last name Middle name and Surname. There will be multiple scnerios where I have to write a code to split based on the scnerio. Could you please help me to get the code. I have attached the Names for your reference. A Column is a Name B referes as Correct First and C is correct Last Name and D is a surname. I need a macro to split the A exactly like Column B ,C & D
 
Hi Mcgill,

Thank you for your reply. These name are just samples scnerios. We will take similar kind of Names on a real time basis. That is the reason I am looking for a macro to split these Name. your help is highly appriciated.
 
Wow That works cool... Thank you Mcgill.
But if a text contains , then the second name should come as first name. for eg
REEVES, PEGGY ---- PEGGY SHOULD BE THE FIRST NAME AND REEVES SHOULD BE LASTNAME.

I wrote the formula as :

=IF(ISNUMBER(SEARCH(" ",A12)),PROPER(LEFT(A12,FIND(" ",A12,1)-1)),IF(ISNUMBER(SEARCH(",",A12)),PROPER(RIGHT(A12,LEN(A12)-FIND(",",A12,1))),IF(ISNUMBER(SEARCH(", ",A12)),PROPER(RIGHT(A12,LEN(A12)-FIND(", ",A12,1))))))

however I am getting "," after that eg REEVES, please help
 
This may work...
add to the beginning of your code =IF(ISERROR(FIND(",",A2))
and the end of your code TRIM(RIGHT(A2,LEN(A2)-FIND(",",A2))))
Just replace parts for the Last Name and Sir Name....
=IF(ISERROR(FIND(",",A2)),IF(ISNUMBER(SEARCH(" ",A2)),PROPER(LEFT(A2,FIND(" ",A2,1)-1)),IF(ISNUMBER(SEARCH(",",A2)),PROPER(RIGHT(A2,LEN(A2)-FIND(",",A2,1))),IF(ISNUMBER(SEARCH(", ",A2)),PROPER(RIGHT(A2,LEN(A2)-FIND(", ",A2,1)))))),TRIM(RIGHT(A2,LEN(A2)-FIND(",",A2))))
 
Thank you. There is an other issue. For eg

Name :Kevin J. Corcoran
First Name should be Kevin J
last Name should be Corcoran

How this can be done in formulas?
 
Now I need a formula for this.. If a name contains firstname middle name and last name the middle name first letter should be added in the first name

For EG
RICHARD EARL TORRES

First Name Should be Richard E
Last Name should be Torres

Like wise
Rahul J Segovia Lozano

First Name should be Rahul J
Last Name Should be Segovia Lozano

There will be multiple senerio and all these should be entered in one cell. Based on the values this formula should split and give the answer.

Your help is highly appriciated.
 
Last edited:
Try.....

1] In "First Name" E2, formula copy down :

=IF(ISNUMBER(FIND(",",A2)),TRIM(RIGHT(A2,LEN(A2)-FIND(",",A2))),IFERROR(LEFT(A2,-LOOKUP(1,-FIND({" Van "," Saint "," La "," Karm "," De "," Allis-Van "},A2&" "))-1),LEFT(A2,FIND(" ",A2,((LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",))-OR(ISNUMBER(SEARCH({" I "," II "," III "," IV "," JR "," SR "},A2&" "))))>1)*FIND(" ",A2)+1)-1)))

2] In "Last Name" F2, formula copy down :

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,E2,""),",",""),IF(G2="","",G2),""))

3] In "Sur Name" G2, formula copy down :

=IFERROR(LOOKUP(1,-SEARCH({" I "," II "," III "," IV "," JR "," SR "},A2&" "),{"I","II","III","IV","JR","SR"}),"")

4] See attachment

Regards
Bosco
 

Attachments

  • First Name Last Name.xlsx
    22.2 KB · Views: 12
Is the middle name always changed to the 1st letter of the name?
Example: RICHARD EARL TORRES = Richard E Torres,
What about VALENZUELA,RANDALL ANN?
Are you matching name variations to a name? Just, asking because it may be easier to start with the standard name and find a matching variation of the name.
 
Yes Middle name will always change the first letter of the name. For VALENZUELA,RANDALL ANN- RANDALL A should be the first name and VALENZUELA should be the last name. And JR or SR, III or II or I these names should be comes in a different column as Suffix. All these criterias should be come in a single cell.
 
Last edited:
You must log in or register to reply here.
Back
Top