2

When I use this formula to import data

=iferror(INDEX(IMPORTHTML("http://www.thestreet.com/quote/GE/details/company-profile.html","table",3),8,1))

The result is Sector: Industrial Goods. I'd like to use the LEFT function to remove the Sector: text and show only Industrial Goods. I can't seem to get the syntax right. I can use this formula to show the length of the string:

=len(iferror(INDEX(IMPORTHTML("http://www.thestreet.com/quote/GE/details/company-profile.html","table",3),8,1)))

But can't figure out how to incorporate the LEFT function to get my result.

1

Short Answer

Instead of LEFT() use RIGHT().

Explanation

The LEFT() function returns the leftmost characters, but the OP want to get the rightmost characters.

The following is a simplified version of the final formula, for explanatory purposes:

=RIGHT("Sector: Industrial Goods",16)

The following is the final formula; in order to improve readability, each parameter is inserted in a new line:

 =RIGHT(
   iferror(
     INDEX(
       IMPORTHTML(
         "http://www.thestreet.com/quote/GE/details/company-profile.html",
          "table",
          3
        ),
        8,
        1
     )
   ),
   16
 )
1

There are a couple ways to do it:

If you specifically want to get rid of only that one element that shows up as "Sector" you can use the substitute function:

=substitute(iferror(INDEX(IMPORTHTML("http://www.thestreet.com/quote/GE/details/company-profile.html","table",3),8,1)),"Sector: ","")

alternatively, if you would like the function to remove any of the tag names, not only the sector one, you can use regexreplace:

=regexreplace(iferror(INDEX(IMPORTHTML("http://www.thestreet.com/quote/GE/details/company-profile.html","table",3),8,1)),".*\: ","")

You could also directly import that element using importxml. This site your pulling the data from has very organized and specific elements, for example:

=IMPORTXML("http://www.thestreet.com/quote/GE/details/company-profile.html","//*[@class='sectorName']")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.