Adding Numbers From Web Pages

[Guest]

I've copied some data from a webpage and pasted it into excel. Some of the
data are numbers that I'm trying to add but I can't get excel to recoginze
the data. I can't get the numbers to format as currency. Any ideas?

 

[Guest]

Right click the cells you need changed, go to the Number tab, select Currency
as the type, and choose the settings you want.

 

[Gord Dibben]

Sounds like the numbers are text.

You could try this first.

Format all to General.

Copy an unused empty cell.

Select the range of data and Edit>Paste Special(in place)>Add>OK>Esc.

If this doesn't coerce the text to numbers, you could have non-breaking
spaces....CHAR 160 which you will need a macro to get rid of.

Post back with your results if trick one doesn't do the job.


Gord Dibben MS Excel MVP

 

[Guest]

Gord,

Tried the paste special and Excel still doesn't recognize. What is the
marco option?

Thanks for your help!

 

[Guest]

If A1 contains a pseudo-number, then try:

=(SUBSTITUTE(SUBSTITUTE(A1," ",""),CHAR(160),""))*1

 

[Harlan Grove]

If A1 contains a pseudo-number, then try:

=(SUBSTITUTE(SUBSTITUTE(A1," ",""),CHAR(160),""))*1

Removing standard ASCII spaces is superfluous. Excel has no problem
ignoring them, e.g., =1+2+" 3 " returns 6 (with Transition
Formula Evaluation disabled).

Also no need for formulas. Select the range, run Edit > Replace, clear
the Find what box and before leaving it hold down an [Alt] key and
press in sequence 0 1 6 0 keys on the numeric keypad on the right side
of the keyboard. That should enter a nonbreaking space in the Find
what box. Clear the Replace with box, and click on the Replace All
button. That should convert all numeric strings to numbers.

 

[Gord Dibben]

Sub Remove_NON_BRK_SPC()
Selection.Replace What:=Chr(160), Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub


Gord