Importing selective external data from a webpage

Hi there, when importing a new web query how can i be more selective in the data i import?

Last Trade: 1,521.30 p
Trade Time: 9:53am
Change: Up 28.30 (1.90%)
Prev Close: 1,493.00
Open: 1,503.00
Bid:1,521.00
Ask:1,522.00
1y Target Est:1,442.50 p

Above is the minimum data that the web query allows me to import, however most of this is useless to me. I've imported this data on to a separate sheet within my workbook, and then inturn use this raw data as a reference to my main spreadsheet. The only bit of information i need is taken from cell B1 '1,521.30 p' however because this cell contains the letter p it doesn't allow the formulas to calculate on my main spreadsheet. Is there any way i can lose the p so when i update my spreadsheet it only uses the numerical value in that cell and ignores any lettering? I'm sure there must be a simple way around this, but i'm really struggling so will appreciate any help. Thanks

WTB

How about this: wherever you use a reference to B1, instead use

(SUBSTITUTE(B1," p","")*1)

So, if you had a formula like

=B1+A5

use this instead:

=(SUBSTITUTE(B1," p","")*1)+A5

The Substitute part will replace the space and p with nothing, and the multiplication with 1 coerces the remaining text string into a number.

Alternatively, with double unary, avoiding a multiplication calculation and saving processing time:

=--(SUBSTITUTE(B1," p",""))+A5

hth

Originally Posted by teylyn

Alternatively, with double unary, avoiding a multiplication calculation and saving processing time:

=--(SUBSTITUTE(B1," p",""))+A5

FWIW: the explicit coercion by means of addition negates need for double unary in first instance, ie:

=SUBSTITUTE(B1," p","")+A5

will do the same thing with one less operation

FWIW: the explicit coercion by means of addition negates need for double unary in first instance, ie:

True, but if you have the output in a cell of its own without any calculation, you need the coercion to make the value available to calls from other cells.

A1 =--(SUBSTITUTE(B1," p",""))

needs the coercion so C1 can do SUM or whatever on A1

Not denying that but then that was not the point I was making... and that was not the purpose of your formula on which my comment was based