Organizing a Name in a cell

[chillyp]

So the problem I have is I have an Excel Spreadsheet that has 1000 names that I'm trying to merge into word to print labels. Now I can do the merging but I need to format the indivual cells from this:

Smith, Dan
Johnson, Bob
Parker, Sara
Tucker, Lance

To this:
Dan Smith
Bob Johnson
Sara Parker
Lance Tucker

I need to remove switch the name from Last, First to First Last and then delete the comma. Any ideas? I've tried to setup a macro but I failed miserably.

 

[mobell]

use macro

Sub Macro1()

Dim str() As String, celEach As Range

For Each celEach In Selection

If InStr(1, celEach.Text, ",", vbTextCompare) >= 1 Then

str() = Split(celEach.Text, ",", , vbTextCompare)
celEach.Value = str(1) & " " & str(0)
​

End If
​

Next
​

End Sub

select the range you want to change, then run this macro.

 

[chillyp]

Mobell, thanks for your help that works perfect. I've been racking my brain over this.

 

[mobell]

You are welcome.