How to reverse last name/first name seperated by a comma

[revwren]

Anyone know how I can either reverse last name/first name in a column or put the first name in one column and the second name in another? Currently, the names are: last name, first name in one column. I need to do a merge in InDesign for table tents.
Thanks.

 

[Rick Rothstein]

Hope there is a way.....
I have entered my contacts into Outlook 2010 and exported them as a Windows CSV file to my desktop, I entered the contacts in all relevant fields as First Name and Last Name; John Smith but the exported CSV file has these as Last Name-Comma-First Name.

Give this formula a try...

=MID(A1&", "&A1,FIND(" ",A1)+1,LEN(A1)+1)

 

[AJW1100]

Hi Rick.
Thank you for your reply, I entered the formula in an inserted column B and ran it, the result was; John , Smith, I think there is a space after John so nearly correct!

I need to import the contacts into Google Contacts so would need to remove the column A leaving only B with names the right way round and no comma's. I think I need to find why Outlook exports names as last,first or why Excel CSV imports in that way assuming Outlook exports it the right way round, if you see what I mean! Somewhere names are getting reversed.

 

[Rick Rothstein]

Hi Rick.
Thank you for your reply, I entered the formula in an inserted column B and ran it, the result was; John , Smith, I think there is a space after John so nearly correct!

If all of your data has that trailing space, then you can use this formula...

=MID(TRIM(A1)&", "&A1,FIND(" ",A1)+1,LEN(A1))

On the other hand, if your data has a mixture of some with and some without the trailing space, then you can use this formula instead...

=MID(TRIM(A1)&", "&A1,FIND(" ",A1)+1,LEN(A1)-(RIGHT(A1)=" ")+1) &"<"

 

[codeliftsleep]

inventing the wheel again
select your range to convert and run the code

Option Explicit

Sub swap_names()
'Erik Van Geit
'061107
Dim rng As Range
Dim arr As Variant
Dim i As Long
Dim j As Integer
Dim ch As Integer

Const sep = ","

If TypeName(Selection) <> "Range" Then Exit Sub

Set rng = Selection
    
    If rng.Count > 1 Then
    arr = rng
    Else
    ReDim arr(1 To 1, 1 To 1)
    arr(1, 1) = rng
    End If

    For i = 1 To UBound(arr, 1)
        For j = 1 To UBound(arr, 2)
        ch = 0
        ch = InStr(1, arr(i, j), sep)
        If ch <> 0 Then arr(i, j) = Mid(arr(i, j), ch + 1) & sep & Left(arr(i, j), ch - 1)
        Next j
    Next i

rng = arr
Erase arr

End Sub

Way too much code. Set whatever range you need and then loop through it.

  Private sub reverseNames
    Dim myArr as Variant
    With Sheets("MySheet")
       myArr= Split(.Range("A1"), ",")
       .Range("B1)= myArr(1) & "," myArr(0)
    End With
  End sub

[\code]

 

[Rick Rothstein]

Way too much code. Set whatever range you need and then loop through it.

  Private sub reverseNames
    Dim myArr as Variant
    With Sheets("MySheet")
       myArr= Split(.Range("A1"), ",")
       .Range("B1)= myArr(1) & "," myArr(0)
    End With
  End sub

[\code][/QUOTE]
First off, you responded to a thread that was almost 12 years old. Second, you alluded to using a loop, but then did not do so in your code, so your code would be at minimum two code lines longer. Once you add them in, then I would say to you "way too much code".:diablo: Assuming the cells in Column A have been selected (what the code you responded to assumed), this would produce the desired result in Column B...
[CODE][table="width: 500"]
[tr]
	[td]Sub ReverseNames()
  Selection.Offset(, 1) = Evaluate(Replace("IFERROR(INDEX(MID(@&"",""&@,FIND("" "",@),LEN(@)+1),0),"""")", "@", Selection.Address))
End Sub[/td]
[/tr]
[/table]

 

[AJW1100]

Hi Rick.

If all of your data has that trailing space, then you can use this formula...

=MID(TRIM(A1)&", "&A1,FIND(" ",A1)+1,LEN(A1))

On the other hand, if your data has a mixture of some with and some without the trailing space, then you can use this formula instead...

=MID(TRIM(A1)&", "&A1,FIND(" ",A1)+1,LEN(A1)-(RIGHT(A1)=" ")+1) &"<"

Thanks for the new formula, the first one returns: John, Smith,
The second one returns: John, Smith,<

My expertise mostly consists of Ctrl C and Ctrl V I have just about got to grips with SUM= and simple + - & / commands.

The formula of =MID(A4,SEARCH(",",A4)+1,999) &""& LEFT(A4,SEARCH(",",A4)-1) works fine if I remove the comma in &","& LEFT. Cell shows John Smith leaving in the comma shows as John, Smith

I just tried an experiment and installed Office 2003 on my wife's laptop. made one contact as John Smith after installing outlook translator I exported the contact to Excel 2003 as CSV. Worked perfect, John in first name column and Smith in last name column.

Maybe someone here could check their Outlook 2010 and see if their contacts export into Excel as 'John Smith' or 'Smith , John' ?

Perhaps MS designed it to work Last Name First Name in Office 2010 and first Name Last Name in Office 2003 ??

 

[jtakw]

Hi,

Check this thread on how to upload attachments, samples, alternatively, you can upload to a file share site like DropBox.

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[AJW1100]

Think I may have found one reason, Mapping of fields, the top field of the right hand box showed 'Name' I deleted this and copied the First Name and Last Name from the list on the left to the list on the right. Generated another contact and opened it in Excel the A column showed the First Name and the B column showed the Last Name.
There was no column showing 'Smith, John'.

I will play a bit more to see if that was the only problem. but if it was, then I have spent days trying to sort it.
As they say, the question is only easy if you already know the answer!

Hope that's sorted it, and may I offer my thanks to all that have tried to help me.

 

[ironwood90242]

Hi, revwren
Welcome to the Board !!!!!

for a formulapproach, try this
=MID(A1,SEARCH(",",A1)+1,999) &","& LEFT(A1,SEARCH(",",A1)-1)

kind regards,
Erik

That helped a lot! Thanks! (now I need to convert from Excel to Access query)