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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
I just want to make sure, is this really all in one cell?

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

rolandeckstein

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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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

New Member
Joined
Aug 3, 2010
Messages
24

ADVERTISEMENT

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

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939

ADVERTISEMENT

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.
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,647
Messages
5,597,363
Members
414,139
Latest member
okela0

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
Top