route217
asked on
macro to split name(s) people data
Hi Experts
Need a macro to split the first name and surnames of people according to the following rules:
1. If column b2 onwards has a person name as Joe Mark Robinson and a blank cell in column A2 so you would see the following in a excel worksheet
Column A2 Column B2
empty cell Joe Mark Robinson THEN DO THE FOLLOWING
Robinson (surname only)
Joe Mark
(first name & Middle name or initial) - if no middle name then or initial then first name ONLY
so i want the macro to look down column B and split the name according to the above example.
If cell A2 onwards is empty look in corresponding cell B2 if a name is present split the name according to example shown.
Need a macro to split the first name and surnames of people according to the following rules:
1. If column b2 onwards has a person name as Joe Mark Robinson and a blank cell in column A2 so you would see the following in a excel worksheet
Column A2 Column B2
empty cell Joe Mark Robinson THEN DO THE FOLLOWING
Robinson (surname only)
Joe Mark
(first name & Middle name or initial) - if no middle name then or initial then first name ONLY
so i want the macro to look down column B and split the name according to the above example.
If cell A2 onwards is empty look in corresponding cell B2 if a name is present split the name according to example shown.
ASKER
Hi patrick,
Will provide dummies workbook
Will provide dummies workbook
ASKER
file attached with made up data
sample.xls
sample.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi pari
does yur code post the final data in a new worksheet????? if so can you amend to remain in same worksheet.
Patrick thanks for the feedback....excellent
does yur code post the final data in a new worksheet????? if so can you amend to remain in same worksheet.
Patrick thanks for the feedback....excellent
ASKER
part two here the exact opposite to this question...........column A2 with names and column B2 onwards empty
https://www.experts-exchange.com/questions/25095362/macro-to-split-names-people-part-2.html
https://www.experts-exchange.com/questions/25095362/macro-to-split-names-people-part-2.html
It would be useful to see some sample data and/or a sample file. EE now allows you to directly upload files
to your question.
Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible
to fully and permanently delete it. Therefore, be very careful about posting proprietary, confidential, or
other sensitive information. If necessary, use "fake" and/or obfuscated data in your sample.
Please note that at present EE restricts uploads to certain file types. If your file type does not match
those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run
by people connected to EE.
Regards,
Patrick