Excel text split

Question

Sample

Modified and self-produced (d)

Food packages (d,d,d,d,e,e,d,d,e,d,d)

Other country’s food labels (d)

Using food labels to choose foods for Healthy eating (d)

Measuring cups food models (d)

Food labelling exercise – custom (e) 

Food product promotional pamphlet (d)

It pays to read your grocery label (d)

Old food labels (d)

Self-produced worksheet to help students navigate through reading real packages (e,e)

Products from the kitchen (e)

Grocery store tour – reading labels on foods (d)

Essentially I want to split the text between parentheses into separate Excel entries.

Modified and self-produced |||| (d)

Food packages |||| (d,d,d,d,e,e,d,d,e,d,d)

Other country’s food labels |||| (d)

How would I go about doing this?

Answer 1

If the data truly is in parentheses you can use a function to do the split

To get what's in the prarenthes, use in A3 (with your data in A1)

A3 =RIGHT(A1,LEN(A1)+1-FIND("(",A1,1))

Then you can get what's left by using in A2

A2 =LEFT(A1,LEN(A1)-LEN(A3))

You may also wish place the following around the formulas to clean up extra blank characters.

trim()

Let me know if this isn't working for you.

Answer 2

Are you saying that you want each comma-delimited element inside the parentheses to each be on a separate column?

If so, you can do this:

• Paste the data into a blank sheet.
• Replace all of the left- and right-parentheses with commas.
• Select the column that has all the data, and then select Data / Text to Columns.
• Select Delimited, then select Comma as the delimiter.

Answer 3

Another option is to use Data - Text to Column and split on the open paren. You will lose the open paren, however, so it may not be as good an option as the formula.