[SIZE="3"]An application I use at work exports names in a first name-last name format. At this point I have a list of about 315 names in this format. Is there a way I can convert these names to a last name-first name format? I need to export these data on a daily basis, so I'll need to perform this conversion every day. I did a search for this, but didn't find anything.
Thank you in advance.[/SIZE]
Re: First Name-last Name To Last Name-first Name
OverKnight,
A couple of questions first
Is the seperator a "-" or a gap ??
Are you after code or a formula.
Do any names have a middle name
A few examples would help
VBA Noob
Re: First Name-last Name To Last Name-first Name
Use Data/Text to Columns and then concatenate the results the way you want it to read
Re: First Name-last Name To Last Name-first Name
The list is of physicians. Some have middle initials, some don't. There are spaces between the first and last names. The export data I receive are like this: John B. Smith, M.D. What I'd like to convert this to is: Smith, John B. The export data include patient names, account numbers, dates of service, the MD names, and more. I need the MD names in the last name-first name format so sorting can be better performed. Concerning code vs. formula, I'm not sure; ideally, there would be some form of lookup table (which I don't know how to do) that I could run that would have every MD name, so please tell me the best method to accomplish this. I'm sure I'd have to update this table as new MD's came on board.
Thank you.
Re: First Name-last Name To Last Name-first Name
See how you fair with this formula first as it sounds like you have lots of variables so it may not work for them all.
Quote=SUBSTITUTE(TRIM(MID(A1,FIND(".",A1)+1,FIND(",",A1))),"M.D.","")&LEFT(A1,FIND(".",A1))
VBA Noob
Re: First Name-last Name To Last Name-first Name
Thank you. This does work on every name with a middle initial. However, if there's no middle initial, it converts John Smith, M.D. to D.John Smith, M. Any ideas for this?
Thanks again.
Re: First Name-last Name To Last Name-first Name
Maybe
Quote=IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))=3,SUBSTITUTE(TRIM(MID(A1,FIND(".",A1)+1,FIND(",",A1))),"M.D.","")&LEFT(A1,FIND(".",A1)),SUBSTITUTE(TRIM(RIGHT(A1,LEN(A1)-FIND(" ",A1)))," M.D.","")&LEFT(A1,FIND(" ",A1)))
VBA Noob
Re: First Name-last Name To Last Name-first Name
You guys are amazing...
This is almost perfect. I feel as though I'm being picky now, but on the names without a middle initial, there's no space between the comma and the first name, i.e., Smith,John.
Re: First Name-last Name To Last Name-first Name
No problem.
Try
Quote=IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))=3,SUBSTITUTE(TRIM(MID(A1,FIND(".",A1)+1,FIND(",",A1))),"M.D.","")&LEFT(A1,FIND(".",A1)),SUBSTITUTE(TRIM(RIGHT(A1,LEN(A1)-FIND(" ",A1)))," M.D.","")&" "&LEFT(A1,FIND(" ",A1)))
VBA Noob
Re: First Name-last Name To Last Name-first Name
Perfect!!!
Thanks so much!
Re: First Name-last Name To Last Name-first Name
Bit late and I havnt really read your post properly because I see noob has posted a solution but this is some old code I had that just reverses names.
Will probably need to be tidyd up.
Sub SwapNames()
'Late binding
'Dimension the RegExp objects
Dim lLoop As Long
Dim stOutput As String
Dim rCell As Range, rCells As Range
Dim oRegEx As Object
Dim oRegMatchColl As Object
' create the RegExp Object with late binding
Set oRegEx = CreateObject("vbscript.regexp")
' set the RegExp parameters
With oRegEx
'look for global matches
.Global = True
'ignore case
.IgnoreCase = True
'look for any word string.
.Pattern = "[a-z]+"
End With
Set rCells = ActiveSheet.Range("A2:A10")
For Each rCell In rCells
stOutput = ""
Set oRegMatchColl = oRegEx.Execute(rCell.Value)
If oRegMatchColl.Count > 0 Then
stOutput = ""
For lLoop = oRegMatchColl.Count - 1 To 0 Step -1
stOutput = stOutput & " " & oRegMatchColl(lLoop)
Next
rCell.Offset(0, 1) = stOutput
End If
Next rCell
Set oRegMatchColl = Nothing: Set oRegEx = Nothing: Set rCells = Nothing
End SubRe: First Name-Last Name To Last Name-First Name Some With Middle Initials
Thank you, Reafidy. I think it's about time that I sit down and finally learn VBA. Pivot tables, too.
Don’t have an account yet? Register yourself now and be a part of our community!