Truncate Text returned from indexhtml

Question

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.

EDIT Cell formula below.

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

EDIT

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?

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.