Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split 1 column to 3 columns | Excel Discussion (Misc queries) | |||
how do i split names in to two columns | Excel Discussion (Misc queries) | |||
How do I split a cell into multiple rows/columns? | Excel Discussion (Misc queries) | |||
split column into 4 columns | Excel Discussion (Misc queries) | |||
Can Columns be split? | Excel Discussion (Misc queries) |