Split Cell Across Two Columns
 

Is there a way for me to change:

Smith, John and Jane

to:

John Smith Jane Smith

Thanks in advance,
Cathy

Split Cell Across Two Columns
 

Showing alternates

Sub splitnames()
c = ActiveCell
findcomma = InStr(c, ",")
fand = InStr(c, "and")
x = Mid(c, findcomma + 1, fand - findcomma - 2) & " " & Left(c, findcomma -
1)
ActiveCell.Offset(, 1) = x
ActiveCell.Offset(, 2) = Right(c, Len(c) - fand - 2) & " " & Left(c,
findcomma - 1)
End Sub
'more than one
Sub splitnamesloop()
For Each c In Range("a2:a16")
findcomma = InStr(c, ",")
fand = InStr(c, "and")
'each line below is ONE line
c.Offset(, 1) = Mid(c, findcomma + 1, fand - findcomma - 2) & " " & Left(c,
findcomma - 1)
c.Offset(, 2) = Right(c, Len(c) - fand - 2) & " " & Left(c, findcomma - 1)
Next
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CCarter" wrote in message
...
Is there a way for me to change:

Smith, John and Jane

to:

John Smith Jane Smith

Thanks in advance,
Cathy

Split Cell Across Two Columns
 

On Sat, 23 Feb 2008 05:29:01 -0800, CCarter
wrote:

Is there a way for me to change:

Smith, John and Jane

to:

John Smith Jane Smith

Thanks in advance,
Cathy

This should parse names in that general format into adjacent columns. To enter
this macro, <alt-F11 opens the VBEditor. Ensure your project is highlighted
in the project explorer window, then Insert/Module and paste the code below
into the window that opens.

To use this, select the range of cells you wish to process. Ensure there is
nothing valuable in the two adjacent columns as the contents will be erased.
(This behavior can be altered when you specify your requirements more
completely).

<alt-F8 opens the macro dialog box. Select ParseNames and <run

==============================
Sub ParseNames()
Dim c As Range
Dim Str As String
Dim mc As Object
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Global = True
re.Pattern = "(^[^,]*),\s*((.(?!and))*)(?:\s*and\s*)?(.*$)"
For Each c In Selection
With c
.Offset(0, 1).ClearContents
.Offset(0, 2).ClearContents
Str = .Value
.Offset(0, 1).Value = re.Replace(Str, "$2 $1")
If re.test(Str) = True Then
Set mc = re.Execute(Str)
If Len(mc(0).submatches(3)) 0 Then
.Offset(0, 2).Value = re.Replace(Str, "$4 $1")
End If
End If
End With
Next c
End Sub
=====================================
--ron

Split Cell Across Two Columns
 

Assuming all your "double names" are structured as shown (with the comma and
the word "and"), give these two formulas (which assume your names are in A1)
a try...

B1: =MID(A1,FIND(",",A1)+2,IF(ISERROR(FIND(" and ",A1)),LEN(A1)+1,FIND("
and ",A1))-FIND(",",A1)-2)&" "&LEFT(A1,FIND(",",A1)-1)

C1: =IF(ISERROR(FIND(" and ",A1)),"",MID(A1,FIND(" and ",A1)+4,99)&"
"&LEFT(A1,FIND(",",A1)-1))

You can then copy these down as required. By the way, these formulas should
work whether there is a second name attached with the word "and" in Column A
or not.

Rick


"CCarter" wrote in message
...
Is there a way for me to change:

Smith, John and Jane

to:

John Smith Jane Smith

Thanks in advance,
Cathy

Split Cell Across Two Columns
 

On Sat, 23 Feb 2008 13:23:58 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Assuming all your "double names" are structured as shown (with the comma and
the word "and"), give these two formulas (which assume your names are in A1)
a try...

B1: =MID(A1,FIND(",",A1)+2,IF(ISERROR(FIND(" and ",A1)),LEN(A1)+1,FIND("
and ",A1))-FIND(",",A1)-2)&" "&LEFT(A1,FIND(",",A1)-1)

C1: =IF(ISERROR(FIND(" and ",A1)),"",MID(A1,FIND(" and ",A1)+4,99)&"
"&LEFT(A1,FIND(",",A1)-1))

You can then copy these down as required. By the way, these formulas should
work whether there is a second name attached with the word "and" in Column A
or not.

Rick

It does give an error if there's no comma. But ...

A suggestion, Rick:

Your formulas are multi-line and break on the <space after the <". I have
found that if I break these long formulas in the formula bar, at an appropriate
length, using <alt-enter, I can then paste them into my newsreader and they
can be copy/pasted directly back into a formula bar, and work with no further
editing.
--ron

Split Cell Across Two Columns
 

Assuming all your "double names" are structured as shown (with the comma
and
the word "and"), give these two formulas (which assume your names are in
A1)
a try...

B1: =MID(A1,FIND(",",A1)+2,IF(ISERROR(FIND(" and ",A1)),LEN(A1)+1,FIND("
and ",A1))-FIND(",",A1)-2)&" "&LEFT(A1,FIND(",",A1)-1)

C1: =IF(ISERROR(FIND(" and ",A1)),"",MID(A1,FIND(" and ",A1)+4,99)&"
"&LEFT(A1,FIND(",",A1)-1))

You can then copy these down as required. By the way, these formulas
should
work whether there is a second name attached with the word "and" in Column
A
or not.

It does give an error if there's no comma. But ...

Hmm! Based on what the OP posted, it didn't seem likely that there would not
be a comma... the names appeared to be arranged as last-comma-first (plus
possibly a second name). What would be listed if there was not comma...
first-last? Well, it is fixable, just in case...

B1: =IF(ISERROR(FIND(",",A1)),A1,MID(A1,FIND(",",A1)+2 ,IF(ISERROR(FIND(
" and ",A1)),LEN(A1)+1,FIND(" and ",A1))-FIND(",",A1)-2)&" "&LEFT(A1,FIND(
",",A1)-1))

No change is needed for the formula in C1.


A suggestion, Rick:

Your formulas are multi-line and break on the <space after the <". I
have
found that if I break these long formulas in the formula bar, at an
appropriate
length, using <alt-enter, I can then paste them into my newsreader and
they
can be copy/pasted directly back into a formula bar, and work with no
further
editing.

Yes, good point.


Rick

Split Cell Across Two Columns
 

On Sat, 23 Feb 2008 15:50:03 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Hmm! Based on what the OP posted, it didn't seem likely that there would not
be a comma... the names appeared to be arranged as last-comma-first (plus
possibly a second name).

Oh, I agree.

What would be listed if there was not comma...

I would think just a last name, or possibly a company name, or possibly a
mistake (in which case an error return would be appropriate).

--ron

Split Cell Across Two Columns
 

What would be listed if there was not comma...

I would think just a last name, or possibly a company name, or possibly
a mistake (in which case an error return would be appropriate).

What I posted would work for a last name only, not sure how you would
distinguish a mistake over a last name only (unless you meant that was the
mistake), but a company name has interesting consequence. Ford Motor Company
would work fine, but a little bit more problematic would be Apple, Inc.

Rick

Split Cell Across Two Columns
 

On Sat, 23 Feb 2008 16:16:41 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

What I posted would work for a last name only, not sure how you would
distinguish a mistake over a last name only (unless you meant that was the
mistake), but a company name has interesting consequence. Ford Motor Company
would work fine, but a little bit more problematic would be Apple, Inc.

Rick

It's amazing how complex a seemingly simple request can sometimes become. I
guess part of that is because the problem is often not completely defined in an
initial posting.
--ron

Split Cell Across Two Columns
 

What I posted would work for a last name only, not sure how you would
distinguish a mistake over a last name only (unless you meant that was the
mistake), but a company name has interesting consequence. Ford Motor
Company
would work fine, but a little bit more problematic would be Apple, Inc.

Rick

It's amazing how complex a seemingly simple request can sometimes become.
I
guess part of that is because the problem is often not completely defined
in an
initial posting.

And we haven't even addressed a family entry like...

Smith, Bob, Joan, Bill and Sue

<g

Rick

Split Cell Across Two Columns
 

Sue has not been back to say that her need was not as the OP

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron Rosenfeld" wrote in message
...
On Sat, 23 Feb 2008 16:16:41 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

What I posted would work for a last name only, not sure how you would
distinguish a mistake over a last name only (unless you meant that was the
mistake), but a company name has interesting consequence. Ford Motor
Company
would work fine, but a little bit more problematic would be Apple, Inc.

Rick

It's amazing how complex a seemingly simple request can sometimes become.
I
guess part of that is because the problem is often not completely defined
in an
initial posting.
--ron