Reformat Name field in excel

Chrisjack001
Chrisjack001 used Ask the Experts™
on
I have an Excel spreadsheet that I want to reformat column D (PIID) field. I want to reformat it to Last Name, First Name instead of the current format which is FirstName LastName, Degree(s). Can you help me to accomplish this goal. Thanks in advance. Attached is a sample of the spreadsheet.
Sample-Excel.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I dont know if I attached the spreadsheet. Attached is it again
Sample-Excel.xlsx
Top Expert 2016

Commented:
are you populating the excel file from access?


Author

Commented:
No. I am formatting this excel file to load it to a table in access
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2016

Commented:
so, you will be importing the excel file to an access table is that correct?
give more details.
Most Valuable Expert 2014

Commented:
The following function will convert your names.
It assumes only a single space between first name and last
 
Public Function ReArrangeName(myCell As Range) As String
Dim OldName As String
Dim Names() As String

OldName = myCell.Value
'drop title
OldName = Left(OldName, InStr(OldName, ",") - 1)
Names = Split(OldName, " ")
OldName = Names(1) & ", " & Names(0)
ReArrangeName = OldName

End Function

Open in new window

Usage:
in a cell =ReArrangeName(D2)
After you change the names, you could copy the column, Paste Special|Values overtop the originals if desired

Altered sample attached
Sample-Excel.xlsm

Author

Commented:
I need to format this excel file to append it to a table that already has data with a "PIID" field in the format that I am requesting.

Author

Commented:
The format in that field is "LastName, FirstName"
Most Valuable Expert 2014

Commented:
Yes.

Put the formula in d2
Drag it down the whole length of the sheet.
Copy all the resulting cells
Select all the cells in column C
Paste Special...Values
Erase everything in column D
Done.
PIID values changed to new format
Top Expert 2016

Commented:
is this a one time import?

Author

Commented:
This is a one time import

Author

Commented:
where do I put this code in the excel file?

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
 Public Function ReArrangeName(myCell As Range) As String
Dim OldName As String
Dim Names() As String

OldName = myCell.Value
'drop title
OldName = Left(OldName, InStr(OldName, ",") - 1)
Names = Split(OldName, " ")
OldName = Names(1) & ", " & Names(0)
ReArrangeName = OldName

End Function

Toggle HighlightingOpen in New WindowSelect All
Most Valuable Expert 2014
Commented:
Add a module.
Paste it in there.
I included a sample that demo'd the code in my earlier post

Author

Commented:
Thanks for your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial