7

Does anyone know a way to get excel to not include quotations in a value?

I have the formula:

=WEBSERVICE("http://finance.yahoo.com/d/?s="&A3&"&f=p2") 

so that I can pull the % Change in a Stock price from Yahoo finance.

It pulls the number back as "+0.15". the quotations are preventing me from throwing the =NumberValue formula in front of that one so that I can format the cells.

I have the same formula (just a tab bit different URL) for Change in price and it doesn't put quotations around it.

1

2 Answers 2

8

Use SUBSTITUTE to find and replace text - =SUBSTITUTE(some_text,"""","") will replace all double quotes with nothing. (There are four double quotes in the second parameter because you have to double them inside strings - otherwise Excel interprets it as the end of the string). So you could use:

=SUBSTITUTE(WEBSERVICE("http://finance.yahoo.com/d/?s="&A3&"&f=p2"),"""","")
2
  • Beautiful. Works great! Thanks for the help! Jul 2, 2015 at 15:29
  • Found another solution that works just as well. same formula just different input for the formula.... =SUBSTITUTE(some_text,CHAR(34),"") Jul 2, 2015 at 16:31
1

Better still, unless it's a text string, use NUMBERVALUE. P2 is a number. Here's an example:

=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A2&"&f=l1"))

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.