Extract First Name Last Name from One Cell

[sachin29feb]

Hello,

I have a problem to extract first and last name of customer.
In one column I have the customer name A1: AmitSharma, A2: JitendraKumar like that... If there any solution to get solution like B1: Amit; C3: Shrma same for A2: JitendraKumar Result: B2: Jitendra; C3: Kumar

I know the most of the surname's if there any macro where I can put the surname on the search base macro extract the first name and last name in respective columns. Which do not match same as it is I will do manually or I will modify the macro to enter new surnames.

I have very bulky data to sort please help me to get the solution....

I have attached the sample data with desired result.Problem Sample.xlsx

[:) Sixthsense :)]

There is no common identifier to split the data...

[sachin29feb]

I will enter the identifier because sheet having a long list with one surname. If it is possible do with one surname by one...

[:) Sixthsense :)]

Not sure whether you got my point or not. I don't see any text of character in your data based on that which we can use it as splitting method.

[satputenandkumar0]

Dear sachin29feb,

If you enter the Last name in c column try below formula in b column.

Please Login or Register  to view this content.

Regards,
Nandkumar S.

[david.f.mayer]

A macro is much more effective on this problem than formula.

This can be very tricky depending on the format of the name.

If it is in the form "Smith, John J", it is easy, just split on the comma and space.

If it is in the form "Smith John J" the best way to do it is with a macro that splits on delimiter giving segments 1, 2, and 3.

If it is in the form "John J Smith Jr", then things are really tricky.

You set up a list of standard name suffixes such as "Jr", "Sr", "III" etc and peel them off first.

The same may be true if the name looks like this, "Mr John J Smith", so you need to strip out the "Mr", "Mrs" etc.

Always strip out the "." (period or dot) character before doing anything else.

Always trim results before saving.