Extract class names from cells in google spreadsheet

Question

I am new to google spreadsheets & want to copy all class names mentioned in range c3:c51 in to b3. I have tried with filter formula but could not succeed, can you please provide formula for the same, my thanks in advance. The range C3:C51 contains following data:

<!doctype html>
<html lang="em">
<head>
    <meta charset="utf-8"/>
    <title>SSD & ABP Planning 2015-16 </title>
    <link rel="stylesheet" href="css.css">
</head>
<body bgcolor="beige"  class="body"> 
    <div class="big_wrapper">
        <header id="top_header">
            <h1>Planning 2015-16 (Branch)</h1>
        </header>
        <ul id="menu-bar">
            <li class="active"><a href="#">Home</a></li>
            <li>
                <a href="#">Sales Tracker</a>
                <ul>
                    <li><a href="#">Sales Territory</a></li>
                    <li><a href="#">Monthly Sales</a></li>
                    <li><a href="#">Deficit detail-CSE wise</a></li>
                    <li><a href="#">Booking detail-CSE wise</a></li>
                    <li><a href="#">Business detail-RBU & CBU</a></li>
                    <li><a href="#">Business detail-New & Renew</a></li>
                </ul>
            </li>
            <li class="line">
                <a href="#">Services</a>
                <ul>
                    <li><a href="#">Services Sub Menu 1</a></li>
                    <li><a href="#">Services Sub Menu 2</a></li>
                    <li><a href="#">Services Sub Menu 3</a></li>
                    <li><a href="#">Services Sub Menu 4</a></li>
                </ul>
            </li>
        </ul>

        <div id="new_div">
            <section id="main_section" >
                <div class="la1"></div>
            </section>
            <aside id="side_news">
                <h4>Important Points</h4>
                Bloggers rooms is here</aside>
        </div>
        <footer id="the_footer">-For presentation purpose only-</footer>
    </div>

    <script src="jquery.js"></script>
    <script src="main.js"></script>
</body>
</html>

For more clarity please visit following link:

https://docs.google.com/spreadsheets/d/1HeiMY41qisz3GxbiGbgJcQKH-_c-GeObxQYna8CTd0w/edit#gid=0

JPV JPV · Accepted Answer · 2015-04-03T07:26:42

If you would want to extract the parts that are highlighted in your example sheet, you could try

=query(C3:C52, "where C contains 'class' ")

To show all the cells in one cell, try:

=join("",C3:C52)

If you want to extract the class names only, try

=trim(join(" ",arrayformula(iferror(regexreplace(regexextract(regexextract(C3:C52, "(class=(.+?)>.+class=(.+?)>|class=(.+?)>)"), char(34)&"(.+)"&char(34)), char(34)&"(.+)"&char(34)," ")))))

Extract class names from cells in google spreadsheet

1 Answers