1

I'm attempting to pull a particular option value from Yahoo! into Google Sheets. The base URL is: https://finance.yahoo.com/quote/GOOG190418C00800000

When breaking the formula up, I have to concatenate several fields into the last part of the URL. The breakdown is:

https://finance.yahoo.com/quote/ + SYMBOL + DATE + C/P + PRICE

The price has to have 5 integers, followed by a decimal point, followed by 3 integers. If the price does not fill all characters, a zero has to be inserted.

For example: 125.00 has to be 00125.000 and 47.50 would be 00047.500.

What I'm trying to figure out is how to take a price in the format of xx.xx or xxx.xx and add the correct amount of 0s to complete the link.

Please let me know if my explanation above doesn't make sense or I have to add more data. Cells, where data is being pulled from, are arbitrary.

Improve this question
1
  • pls share a copy of your sheet
    – user0
    Apr 18, 2019 at 2:25

1 Answer 1

Reset to default
1

What I'm trying to figure out is how to take a price in the format of xx.xx or xxx.xx and add the correct amount of 0s to complete the link.

=TEXT(A1, "00000.000")

0

=ARRAYFORMULA(IF(LEN(A1:A), TEXT(A1:A, "00000.000"), ))

0

=SUBSTITUTE(TEXT(G7, "00000.000"), ".", "")

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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