asked on

Complex Formula - Excel 2010

TV-Ratings-Sample.xlsxHello experts,

I need to build text formulas for extracting specific parts from the following text, which relates to TV rating:

CBS: "60 Minutes" (14.55 million viewers, 9.1/15 households)
NBC: "Dateline" (8.35 million, 5.4/9)
ABC: "America's Funniest Home Videos" (7.6 million, 4.3/7)
FOX: "Bob's Burgers" rerun (1.9 million, 1.2/2)/"The Cleveland Show" (2.7 million, 1.6/2)

For example, statement #1 should be broken-down into:
CBS
60 Minutes
14.55
9.1
15

statement #2 should be broken-down into:
NBC
Dateline
8.35
4.5
9

In othere words, I need 5 formulas for extracting: Channel name (NBC) , Show Title (Dateline), Audience size (8.35), audience rating (4.5) and Household number (9).

The good news is that the format of statements is standard, as you can see from the above four samples.

Therefore, I would really appreciate your help in creating complex Excel formulas for extracting the above bits.

Thank you,
Hani

Saqib Husain

On convenient way to do this is to replace all symbols namely

: ( ) /

with a comma using the search/replace feature and then perform a text-to-columns operation on the data.

Write back if this is not ok with you

Rory Archibald

Are you expecting the last item to be broken out into two entries?

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saqib Husain

One complex formula for all. Enter this formula in G1 and copy it down. If you want it somewhere else then copy this formula in G1, move it where you want and then copy it down.

=IFERROR(MID(OFFSET($A$1,(ROW()-ROW($G$1))/6,0),FIND(CHOOSE(MOD(ROW()-ROW($G$1),6)+1,"",":","(",",","/",")"),OFFSET($A$1,(ROW()-ROW($G$1))/6,0))+1-(MOD(ROW()-ROW($G$1),6)=0),FIND(CHOOSE(MOD(ROW()-ROW($G$1),6)+1,":","(",",","/",")"),OFFSET($A$1,(ROW()-ROW($G$1))/6,0))+1-FIND(CHOOSE(MOD(ROW()-ROW($G$1),6)+1,"",":","(",",","/",")"),OFFSET($A$1,(ROW()-ROW($G$1))/6,0))-2+(MOD(ROW()-ROW($G$1),6)=0)),"")

Mehawitchi

ASKER

Thank you ssaqibh & rorya - Your solutions are amazing and compliment each other.

ssaqibh - Your formulas extract complete show title, even when we have irregular entries such as:
FOX: "Bob's Burgers" rerun (1.9 million, 1.2/2) ---> additional word (rerun) not inside quotation marks
CBS: 54th annual Grammy Awards (40.7 million, 22.1/32) ---> No quotation marks

rorya
Your formulas for extracting numbers will extract the number (without the 'viewers' or 'households'), which what I need really

Thank you both

Mehawitchi

ASKER

Great stuff