I have imported values from a website using a web query which I will use in
my formulas. However these values are abbreviated with "k" which stands for
thousand, and Microsoft Excel does not seem to be able to work with this
sign. Is there anyway in which I can alter the "k" into a thousand without
touching the web query, and thus enable it to be used in my fomulas?

Find K and replace with nothing...
Then enter 1000 anywhere, copy it, choose the data you want to convert to
thousands and right-click, choose Paste Special|Multiply

"Fred" wrote:

I have imported values from a website using a web query which I will use in
my formulas. However these values are abbreviated with "k" which stands for
thousand, and Microsoft Excel does not seem to be able to work with this
sign. Is there anyway in which I can alter the "k" into a thousand without
touching the web query, and thus enable it to be used in my fomulas?

Hi,

I think you should add a field beside the web query return range which
creates a new column to handle the K. The problem with Replace is that
everytime you refresh your query the K will be back. And worse if the query
is set to automatically refresh every 10 minutes, you would need to write
code to handle the Replace command automatically.

For example the formula
=--LEFT(G2,LEN(G2)-1)
Where entries like 123 K are in column G.

You can also right click the query range and choose Data Range Properties,
and check the option Fill down formulas in columns adjacent to data.

If this helps, please click the Yes button
--
Thanks,
Shane Devenshire


"Fred" wrote:

I have imported values from a website using a web query which I will use in
my formulas. However these values are abbreviated with "k" which stands for
thousand, and Microsoft Excel does not seem to be able to work with this
sign. Is there anyway in which I can alter the "k" into a thousand without
touching the web query, and thus enable it to be used in my fomulas?

Try if formula:

=IF(ISERR(FIND("k",A1,1))=FALSE,LEFT(A1,(FIND("k", A1,1)-1))*1000,A1)


--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India


"ShaneDevenshire" wrote:

Hi,

I think you should add a field beside the web query return range which
creates a new column to handle the K. The problem with Replace is that
everytime you refresh your query the K will be back. And worse if the query
is set to automatically refresh every 10 minutes, you would need to write
code to handle the Replace command automatically.

For example the formula
=--LEFT(G2,LEN(G2)-1)
Where entries like 123 K are in column G.

You can also right click the query range and choose Data Range Properties,
and check the option Fill down formulas in columns adjacent to data.

If this helps, please click the Yes button
--
Thanks,
Shane Devenshire


"Fred" wrote:

I have imported values from a website using a web query which I will use in
my formulas. However these values are abbreviated with "k" which stands for
thousand, and Microsoft Excel does not seem to be able to work with this
sign. Is there anyway in which I can alter the "k" into a thousand without
touching the web query, and thus enable it to be used in my fomulas?