pasting financial data from web

[emile]

I am pasting pricing data from a web page into my spreadsheet. The data
looks like this: 7.091 [in cell A1] When I try to use A1 in an equation I
get #VALUE! as the result. I believe this is because the data pasted is in
a text format not a number. I have tried the formula B1=value(A1) but still
get the same result.

How can I get the pasted data in a data format (currency or number) that I
can use in an equation? Thanks in advance

 

[Gord Dibben]

Could be a couple of things going on.

You have extra spaces in the data.

Try TRIM and CLEAN functions.

You have non-breaking spaces

Try Edit>Replace

What: Alt + 0160

With: nothing


Gord Dibben MS Excel MVP

 

[Don Guillett]

Maybe instead of copy/paste you can use an external query. Post your url and
needs.

 

[emile]

Thanks Gord. Imported data has two extra spaces at the end (7.091 ).
Tried both CLEAN & TRIM, still get #VALUE!. Only way to fix is to manually
delete the two spaces, then formula works fine. Any other suggestions?
Need automated process for values which change daily. Thanks

Could be a couple of things going on.

You have extra spaces in the data.

Try TRIM and CLEAN functions.

You have non-breaking spaces

Try Edit>Replace

What: Alt + 0160

With: nothing


Gord Dibben MS Excel MVP

I am pasting pricing data from a web page into my spreadsheet. The data
looks like this: 7.091 [in cell A1] When I try to use A1 in an equation
I
get #VALUE! as the result. I believe this is because the data pasted is
in
a text format not a number. I have tried the formula B1=value(A1) but
still
get the same result.

How can I get the pasted data in a data format (currency or number) that I
can use in an equation? Thanks in advance

 

[Don Guillett]

Try APPLICATION.trim
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Thanks Gord. Imported data has two extra spaces at the end (7.091 ).
Tried both CLEAN & TRIM, still get #VALUE!. Only way to fix is to
manually delete the two spaces, then formula works fine. Any other
suggestions? Need automated process for values which change daily. Thanks

Could be a couple of things going on.

You have extra spaces in the data.

Try TRIM and CLEAN functions.

You have non-breaking spaces

Try Edit>Replace

What: Alt + 0160

With: nothing


Gord Dibben MS Excel MVP

I am pasting pricing data from a web page into my spreadsheet. The data
looks like this: 7.091 [in cell A1] When I try to use A1 in an equation
I
get #VALUE! as the result. I believe this is because the data pasted is
in
a text format not a number. I have tried the formula B1=value(A1) but
still
get the same result.

How can I get the pasted data in a data format (currency or number) that
I
can use in an equation? Thanks in advance

 

[Gord Dibben]

Did you try the edit>replace with Alt + 0160?


Gord

Thanks Gord. Imported data has two extra spaces at the end (7.091 ).
Tried both CLEAN & TRIM, still get #VALUE!. Only way to fix is to manually
delete the two spaces, then formula works fine. Any other suggestions?
Need automated process for values which change daily. Thanks

Could be a couple of things going on.

You have extra spaces in the data.

Try TRIM and CLEAN functions.

You have non-breaking spaces

Try Edit>Replace

What: Alt + 0160

With: nothing


Gord Dibben MS Excel MVP

I am pasting pricing data from a web page into my spreadsheet. The data
looks like this: 7.091 [in cell A1] When I try to use A1 in an equation
I
get #VALUE! as the result. I believe this is because the data pasted is
in
a text format not a number. I have tried the formula B1=value(A1) but
still
get the same result.

How can I get the pasted data in a data format (currency or number) that I
can use in an equation? Thanks in advance

 

[Harlan Grove]

Try APPLICATION.trim

....

If you're not going to spend any time or effort reading the OP's
follow-ups, why respond?
....

Let me make it easy for you. When would Appplication.Trim (or VBA's
TRIM function) EVER remove what appear to be trailing spaces when the
worksheet functions TRIM and CLEAN didn't?

Another easy question: if the OP's problem is nonbreaking HTML spaces
(decimal character code 160), what would you recommend? As is, what
has been the standard recommendation in Excel newsgroups for removing
spaces from data pasted from web sources for more than a decade? You
have read some of the hundreds of threads that repeatedly cover this,
haven't you?

 

[Frank]

...

If you're not going to spend any time or effort reading the OP's
follow-ups, why respond?


...

Let me make it easy for you. When would Appplication.Trim (or VBA's
TRIM function) EVER remove what appear to be trailing spaces when the
worksheet functions TRIM and CLEAN didn't?

Another easy question: if the OP's problem is nonbreaking HTML spaces
(decimal character code 160), what would you recommend? As is, what
has been the standard recommendation in Excel newsgroups for removing
spaces from data pasted from web sources for more than a decade? You
have read some of the hundreds of threads that repeatedly cover this,
haven't you?

Download a free copy of the add-in ASAP Utilities
and I think you can solve this problem.

 

[Don Guillett]

Harlan, I took the last question to be 7.091 with 2 spaces at the end.

Sub trytrim()
'test with h4 with leading and trailing spaces
With Range("h4")
MsgBox Len(.Value)
MsgBox Len(Application.Trim(.Value))
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Try APPLICATION.trim

....

If you're not going to spend any time or effort reading the OP's
follow-ups, why respond?
....

Let me make it easy for you. When would Appplication.Trim (or VBA's
TRIM function) EVER remove what appear to be trailing spaces when the
worksheet functions TRIM and CLEAN didn't?

Another easy question: if the OP's problem is nonbreaking HTML spaces
(decimal character code 160), what would you recommend? As is, what
has been the standard recommendation in Excel newsgroups for removing
spaces from data pasted from web sources for more than a decade? You
have read some of the hundreds of threads that repeatedly cover this,
haven't you?

 

[Harlan Grove]

Harlan, I took the last question to be 7.091 with 2 spaces at the end.

....

But OP already tried TRIM and CLEAN, and those didn't fix the problem.
If the OP's use of TRIM in worksheet functions didn't fix the problem,
why would you believe Application.Trim (the VBA wrapper around exactly
the same function) would produce a different result?

Since the OP already mentioned trying to use the VALUE function in
formulas, I figure the OP knows how to combine VALUE and TRIM, so the
OP means something like =VALUE(TRIM(somecellref))+1 produces #VALUE!
result. If so, what would your H4 cell need to contain so that your
macro produced a numberic result in H4 but the formula

=VALUE(TRIM(H4))+1

produced #VALUE! in some other cell (maybe I4)?

The OP's problem is almost certainly due to HTML nonbreaking spaces
being used for spacing in the HTML page the OP is copying. Neither
TRIM, CLEAN nor Application.Trim will remove such characters.

Since leading/trailing standard ASCII spaces (decimal character code
32) have no effect on string to number conversions, the OP could
change formulas referring to imported cell H4 to

=SUBSTITUTE(H4,CHAR(160),"")+1

(with Transition Formula Evaluation disabled).

So you've actually never come across a thread in any of the Excel
newsgroups in which HTML nonbreaking spaces caused string to number
conversion problems?

 

[emile]

Thank you all for your help. As much as you may think I am an experienced
function user in Excel - I am not. I don't understand how to do
"edit>replace with Alt + 0160". I don't understand "APPLICATION.trm". I
don't know what "OP" means.

Harlan and Don: You guys are great with the support but maybe you need to
take your personal opinions of each other off line.

I did try the SUBSTITUTE(H4,CHAR(160),"")+1 suggestion. It works without
the +1 at the end. No sure why the +1 was there to start.

Finally, was able to use MSNStockQuote add-in to update the values
automatically.

Thanks again

Did you try the edit>replace with Alt + 0160?


Gord

Thanks Gord. Imported data has two extra spaces at the end (7.091 ).
Tried both CLEAN & TRIM, still get #VALUE!. Only way to fix is to
manually
delete the two spaces, then formula works fine. Any other suggestions?
Need automated process for values which change daily. Thanks

Could be a couple of things going on.

You have extra spaces in the data.

Try TRIM and CLEAN functions.

You have non-breaking spaces

Try Edit>Replace

What: Alt + 0160

With: nothing


Gord Dibben MS Excel MVP


I am pasting pricing data from a web page into my spreadsheet. The data
looks like this: 7.091 [in cell A1] When I try to use A1 in an
equation
I
get #VALUE! as the result. I believe this is because the data pasted is
in
a text format not a number. I have tried the formula B1=value(A1) but
still
get the same result.

How can I get the pasted data in a data format (currency or number) that
I
can use in an equation? Thanks in advance