I'm new to making spreadsheets. I'm making one for a game I play. I'm trying to use an api call to return json data from the games exchange website, to update prices in near real time. However I don't know how to format the returned value for my function into an int to manipulate the number later in other cells. enter image description here

EDIT Cell formula below.

=Index(ImportHtml(CONCAT("http://services.runescape.com/m=itemdb_oldschool/results.ws?query=", encode(B8)), "table", 1),O9,3)*1


Error Function MULTIPLY parameter 1 expects number values. But '37.8k' is a text and cannot be coerced to a number.

As you can see, the game I play runescape. has non standard increments for 10k 100k 1m 2b. And I want to be able to somehow depending upon what is returned to me(k, m, b) multiply the number respectively to match the price. I'm not sure where to start looking for ways to do this?

  • 1
    If I'm understanding this correctly, the actual problem you want solved is how to convert text such as "30k" into the numeric value "30000" right?
    – Emmabee
    Jun 14, 2018 at 17:23
  • @ Emmabee Correct.
    – bannji
    Jun 14, 2018 at 17:33

1 Answer 1

=iferror(value(A1), if(right(A1)="k", left(A1, len(A1)-1)*1000, if(right(A1)="m", left(A1, len(A1)-1)*1000000, if(right(A1)="b", left(A1, len(A1)-1)*1000000000, "Unexpected Value"))))

This will check the contents of A1 and return the numeric value associated with it.

First it will check if trying to turn A1 into a numeric value using in-house methods will produce an error. If not it will simply return that result. This handles all numbers less than 1000.

Next it will check if the right-most character is a k. If it is, it will remove that character and multiply the remaining string by 1000.

If the character was not a k it will check if it's an m. If it is, it will remove that character and multiply the remaining string by 1000000.

If the character was not an m it will check if it's a b, similarly removing and multiplying the number (this time by 1000000000) if it is.

Finally, if the value was not already a number and did not end with a k, m, or b, it will display the string "Unexpected Value" to alert you to the problem.

  • Wow, Thats wonderful thank you. So ill need two boxes to refrence the original formula's return value and use this as the display box for it? ill post an example.
    – bannji
    Jun 14, 2018 at 17:40
  • @bannji Yep, that's right! So just tuck a second sheet away or something that has a column with all the unformatted prices, convert them to numbers in the second column with this, and then go back to the first sheet and pull the numeric values you've sorted out off the helper page :)
    – Emmabee
    Jun 14, 2018 at 17:52

Your Answer

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

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