Extracting and Separating data from one cells into two cells

rolandeckstein

New Member
Joined
Aug 3, 2010
Messages
24
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
I just want to make sure, is this really all in one cell?

John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:

Code:
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
ABC
1John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)John Smith ; Warren Zevon ; Pete Townsend(CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)
2John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)John Smith ; Warren Zevon ; Pete Townsend(CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)
3John Smith (CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)John Smith ; Warren Zevon ; Pete Townsend(CEO); Warren Zevon (CFO); Pete Townsend (VP Operations)
4John 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.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top