How to switch two data cells ?
 

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

How to switch two data cells ?
 

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 C2:D2, 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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mark246" wrote in message
...
I LOVE this forum. Very helpful.

In Excel 2007, I've got 2 columns of data...
FirstName LastName

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

How to switch two data cells ?
 

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" wrote in message
...
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

How to switch two data cells ?
 

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









On Feb 23, 7:04*pm, "Max" wrote:
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 C2:D2, 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

--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---"Mark246" wrote in message

...



I *LOVE *this forum. *Very helpful.

In Excel 2007, I've got 2 columns of data...
FirstName * * * LastName
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- Hide quoted text -

- Show quoted text -

How to switch two data cells ?
 

.. 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 C2:D2, copy down for the desired results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mark246" wrote in message
...
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

How to switch two data cells ?
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---