Extracting and Separating data from one cells into two cells

[rolandeckstein]

Ok, so I have a cell that contains data in the following format:

John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)

I need to setup formulas so that in the first new column the cell would contain:

John Smith; Warren Zevon; Pete Townsend

And the second column would display the following:

CEO; CFO; VP Operations

Is there a way to selectively extract this data so that one column extracts whatever data is in Parenthesis and the other selects the data not in Parenthesis?

Any and ALL help is much appreciated

 

[rolandeckstein]

Ok great. I'm not handy with macro as of yet but am learning so thank you.

 

[schielrn]

Actually this seems to work:

Sub test()
For Each c In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    c.Copy Destination:=c.Offset(0, 1).Resize(, 2)
    c.Offset(0, 1).Replace What:="(*)", Replacement:=""
    For i = 1 To Len(c.Value) - Len(WorksheetFunction.Substitute(c.Value, ";", "")) + 1
        If i = 1 Then
            c.Offset(0, 2).Replace What:=Left(c.Offset(0, 1).Value, InStr(c.Offset(0, 1).Value, ";") - 1), Replacement:=""
            temp1 = Left(c.Offset(0, 2).Value, InStr(c.Offset(0, 2).Value, ";"))
            temp2 = WorksheetFunction.Substitute(c.Offset(0, 2).Value, temp1, "")
        Else
            temp1 = temp1 & " " & Mid(temp2, InStr(1, temp2, "("), InStr(1, temp2, ")") - InStr(1, temp2, "(") + 2)
            temp2 = Mid(temp2, InStr(1, temp2, ";") + 1)
        End If
    Next i
    c.Offset(0, 2).Value = temp1
Next c
End Sub

Note this does start in row 1.

It also assumes that there are at least 2 names in every cell. Not sure what will error if there is not a ; in one of the cells. If that can be the case we will need to account for that.

This also assumes the data starts on row 1 if that is niot the case change the value in bold red to the correct starting cell.

This also works with Columns A, B and C. You will need to adjust as well if that is not the case.

Excel Workbook
	A	B	C
1	John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)	John Smith ; Warren Zevon ; Pete Townsend	(CEO); (CFO); (VP Operations)
2	John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)	John Smith ; Warren Zevon ; Pete Townsend	(CEO); (CFO); (VP Operations)
3	John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)	John Smith ; Warren Zevon ; Pete Townsend	(CEO); (CFO); (VP Operations)
4	John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)	John Smith ; Warren Zevon ; Pete Townsend	(CEO); (CFO); (VP Operations)
5	John Smith (CFO); Warren Zevon (Head of Investor Relations); Pete Townsend (Senior IR Manager)	John Smith ; Warren Zevon ; Pete Townsend	(CFO); (Head of Investor Relations); (Senior IR Manager)
6	John Smith (CFO); Warren Zevon (Head of Investor Relations); Pete Townsend (Senior IR Manager)	John Smith ; Warren Zevon ; Pete Townsend	(CFO); (Head of Investor Relations); (Senior IR Manager)
Sheet2

Hope that helps.

 

[rolandeckstein]

There are instances in which only one name would show up in a cell and would displayed simply as:

John Smith (CEO)

 

[schielrn]

Here you go:

Sub test()
For Each c In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    c.Copy Destination:=c.Offset(0, 1).Resize(, 2)
    c.Offset(0, 1).Replace What:="(*)", Replacement:=""
    c.Offset(0, 1).Value = Trim(c.Offset(0, 1).Value)
    If InStr(1, c.Value, ";") > 0 Then
        For i = 1 To Len(c.Value) - Len(WorksheetFunction.Substitute(c.Value, ";", "")) + 1
            If i = 1 Then
                c.Offset(0, 2).Replace What:=Left(c.Offset(0, 1).Value, InStr(c.Offset(0, 1).Value, ";") - 1), Replacement:=""
                temp1 = Left(c.Offset(0, 2).Value, InStr(c.Offset(0, 2).Value, ";"))
                temp2 = WorksheetFunction.Substitute(c.Offset(0, 2).Value, temp1, "")
            Else
                temp1 = temp1 & " " & Mid(temp2, InStr(1, temp2, "("), InStr(1, temp2, ")") - InStr(1, temp2, "(") + 2)
                temp2 = Mid(temp2, InStr(1, temp2, ";") + 1)
            End If
        Next i
        c.Offset(0, 2).Value = temp1
    Else
        c.Offset(, 2).Replace What:=c.Offset(, 1).Value & " ", Replacement:=""
    End If
Next c
End Sub

Excel Workbook
	A	B	C
1	John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)	John Smith ; Warren Zevon ; Pete Townsend	(CEO); (CFO); (VP Operations)
2	John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)	John Smith ; Warren Zevon ; Pete Townsend	(CEO); (CFO); (VP Operations)
3	John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)	John Smith ; Warren Zevon ; Pete Townsend	(CEO); (CFO); (VP Operations)
4	John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)	John Smith ; Warren Zevon ; Pete Townsend	(CEO); (CFO); (VP Operations)
5	John Smith (CFO); Warren Zevon (Head of Investor Relations); Pete Townsend (Senior IR Manager)	John Smith ; Warren Zevon ; Pete Townsend	(CFO); (Head of Investor Relations); (Senior IR Manager)
6	John Smith (CFO); Warren Zevon (Head of Investor Relations); Pete Townsend (Senior IR Manager)	John Smith ; Warren Zevon ; Pete Townsend	(CFO); (Head of Investor Relations); (Senior IR Manager)
7	John Doe (CFO)	John Doe	(CFO)
Sheet2

Hope that helps.

 

[rolandeckstein]

Ok so, I copied that into Visual Basic Editor, hit run, and there was a runtime error that came up as "invalid pr-ocedure call or argument". it selected the following line as the source during debug:

temp1 = temp1 & " " & Mid(temp2, InStr(1, temp2, "("), InStr(1, temp2, ")") - InStr(1, temp2, "(") + 2)

The code started to work. It got up to row 17 before stopping

 

[schielrn]

Hmmm... That is interesting. Not sure why it would give you that error after running at rows?

What is the data in row 17?

 

[rolandeckstein]

The data in row 17 is:

<TABLE style="WIDTH: 428pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=570><COLGROUP><COL style="WIDTH: 428pt; mso-width-source: userset; mso-width-alt: 20845" width=570><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 428pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=570>Erika Xu; IR Manager

That comes up as the last row that works. The next row, 18 is the first one that does not work. The data in that row is:

<TABLE style="WIDTH: 428pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=570><COLGROUP><COL style="WIDTH: 428pt; mso-width-source: userset; mso-width-alt: 20845" width=570><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 428pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=570>Sally Wang

</TD></TR></TBODY></TABLE>
</TD></TR></TBODY></TABLE>