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

 

[schielrn]

I just want to make sure, is this really all in one cell?

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

 

[rolandeckstein]

Yes, the following is exactly what is in the cell (with the names changed for confidentiality reasons):

John Smith (CFO); Warren Zevon (Head of Investor Relations); Pete Townsend (Senior IR Manager)

It should have been sent to us with the titles that are in parenthesis in a separate column; therefore I now have separate them and am working with a large number of datasets so i need a formula that i can put in one cell and copy down the worksheet.

 

[schielrn]

Are there always 3 names and titles in a cell or can it be varying? Will there always be a ; in between each name, so would this statement be true:

There will be the number of names equal to the number of ; in a cell plus 1.

 

[rolandeckstein]

There are varying names in a cell. Yes that statement is true.

 

[rolandeckstein]

Varying amounts of names*

 

[schielrn]

Do you know the maximum number of names that could be in a cell? I am thinking there may not be an easy formula approach and may have to use VBA code to accomplish this.

 

[JustinStarry]

My code is useless now...

 

[rolandeckstein]

as I can see, six is the max number of names

 

[schielrn]

I can't think of an easy formula approach and I have started with a macro approach, but have to go for now, I will try to get back to it later of look at it again tonight hopefully. But here is the start if you or someone else wants to have a look at it:

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, ";", ""))
        If i = 1 Then
            c.Offset(0, 2).Replace What:=Left(c.Offset(0, 1).Value, WorksheetFunction.Find(";", c.Offset(0, 1).Value) - 1), Replacement:=""
        Else
            
        End If
    Next i
Next c
End Sub

Hope that helps you get started.

This assumes that the whole data is in column A starting in row 1 and that the names are going in column B and the titles in column C.

This is what it looks like currently after the macro runs:

Excel Workbook
	A	B	C
1	John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)	John Smith ; Warren Zevon ; Pete Townsend	(CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)
2	John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)	John Smith ; Warren Zevon ; Pete Townsend	(CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)
3	John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)	John Smith ; Warren Zevon ; Pete Townsend	(CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)
4	John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)	John Smith ; Warren Zevon ; Pete Townsend	(CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)
Sheet2

I'll try to check back tonight.