Split Cell Across Two Columns

  • Thread starter Thread starter CCarter
  • Start date Start date
C

CCarter

Is there a way for me to change:

Smith, John and Jane

to:

John Smith Jane Smith

Thanks in advance,
Cathy
 
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
 
Is there a way for me to change:

Smith, John and Jane

to:

John Smith Jane Smith

Thanks in advance,
Cathy
Click to expand...

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
 
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
 
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
Click to expand...

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
 
Assuming all your "double names" are structured as shown (with the comma
It does give an error if there's no comma. But ...
Click to expand...

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.
Click to expand...

Yes, good point.


Rick
 
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).
Click to expand...

Oh, I agree.
What would be listed if there was not comma...
Click to expand...

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
 
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).
Click to expand...

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
 
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
Click to expand...

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
 
What I posted would work for a last name only, not sure how you would
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.
Click to expand...

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

Smith, Bob, Joan, Bill and Sue

<g>

Rick
 
Back
Top