How to switch two data cells ?

[Mark246]

I LOVE this forum. Very helpful.

In Excel 2007, I've got 2 columns of data...
FirstName LastName
Joe Smith
Mary Jones
but SOME of the data are now in the format...
Doe, John
I want them all like...
John Doe
How can I go thru the columns and...
Find any COMMAs in the FirstName column (that is actually a
LastName),
Anytime there is a comma, Delete the comma,
Move the JOHN to the Previous column, and
Move the SMITH to Next column.


If it takes several steps... no problem.


Thanks very much, people.


Mark246

 

[Max]

This might suffice

Assume source data in cols A and B as posted, from row2 down

In C2:
=IF(ISNUMBER(SEARCH(",",A2)),MID(A2,SEARCH(",",A2)+2,99),A2)

In D2:
=IF(ISNUMBER(SEARCH(",",A2)),LEFT(A2,SEARCH(",",A2)-1),B2)
Select C22, copy down as required

If your data is:

Joe Smith
Mary Jones
Doe, John

(where the "Doe, John" is assumed in a single cell in col A)

you'd get the required results in cols C & D, viz.:
Joe Smith
Mary Jones
John Doe

 

[Rick Rothstein \(MVP - VB\)]

This macro, run from the sheet with the names on it (that is, click Alt+F8
from the sheet and run the macro), should do what you want...

Sub CorrectNames()
Dim X As Long
Dim LastCell As Long
Dim TempName As String
LastCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For X = 1 To LastCell
If InStr(ActiveSheet.Cells(X, "A").Value, ",") Then
TempName = ActiveSheet.Cells(X, "A").Value
ActiveSheet.Cells(X, "A").Value = ActiveSheet.Cells(X, "B").Value
ActiveSheet.Cells(X, "B").Value = Replace(TempName, ",", "")
End If
Next
End Sub

Note: Last Name column assumed to be A, First Name assumed to be column B.

Rick

 

[Mark246]

Thanks for the replies, but that doesn't quite work, Max.
I'm not at all familiar with Macros, so I'm concentrating on the
Function solutiion.

First, the "Doe, John" is not in a single column.
It is in 2 columns, just like the "Mary Jones".
It is in 2 columns, like............."Doe, John".

Still, I tried your function, Max, and...
it took up TWO rows. I cannot figure out why that happened.
Thus, when I copied the function down the column, every other row is
ignored.

A bit more help, please ?

Thanks.

Mark246

 

[Max]

.. First, the "Doe, John" is not in a single column.

.. It is in 2 columns, like............."Doe, John".

Here's a quick sample (amended to suit your clarification above) for easy
reference:
http://www.freefilehosting.net/download/3cg7m
Switch 1st n last names if comma.xls

Source data in cols A & B, from row2 down
In C2:
=IF(ISNUMBER(SEARCH(",",A2)),B2,A2)
In D2:
=IF(ISNUMBER(SEARCH(",",A2)),LEFT(A2,SEARCH(",",A2)-1),B2)
Select C22, copy down for the desired results

---
Thanks for the replies, but that doesn't quite work, Max.
I'm not at all familiar with Macros, so I'm concentrating on the
Function solutiion.

First, the "Doe, John" is not in a single column.
It is in 2 columns, just like the "Mary Jones".
It is in 2 columns, like............."Doe, John".

Still, I tried your function, Max, and...
it took up TWO rows. I cannot figure out why that happened.
Thus, when I copied the function down the column, every other row is
ignored.

A bit more help, please ?
Thanks.
Mark246

 

[Max]

Still, I tried your function, Max, and...

it took up TWO rows.

When you copy the formulas n paste direct from the response into the formula
bars, you'd usually need to clean up the "extra stuff". This is due to
inadvertent line wraps & what-nots introduced by the medium/newsreader

---