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"))

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .