Downloaded numbers from the web are stored as text. No Good
 

Greetings Once Again,

I'm downloading data from a website using VBA and for some unknown reason
Excel is storing numbers as text and dates in mm/dd/yy instead of mm/dd/yyyy.
The strange thing is this doesn't happen for all the data that is downloaded.
It appears to be random.

Why is this and How can I fixed this?

I tried using the format cells, but that didn't work. I get that
notification (that green triangle) and I could update each cell as required,
but I have hundreds of cells to go through.

Thanks As Always! Kurt

Downloaded numbers from the web are stored as text. No Good
 

all of your dates are in one or random columns?

Downloaded numbers from the web are stored as text. No Good
 

All the numbers are in one column and the dates are all in another column

" wrote:

all of your dates are in one or random columns?

Downloaded numbers from the web are stored as text. No Good
 

For the numbers, just put 1 in a cell, copy that, then select all the
numbers and paste special: multiply. Excel will convert the text to
numbers and then multiply by 1, thus turning them into numbers. As for
the dates, not sure.
k. moran wrote:
All the numbers are in one column and the dates are all in another column

" wrote:

all of your dates are in one or random columns?

Downloaded numbers from the web are stored as text. No Good
 

For the date fields just use the TEXT function: =TEXT(a3,"mm/dd/yyyy").
That will convert your numbers to a usable format. Of course you can
use whatever date format that you'd like, not just the example that I
gave. HTH.

Harold

John Fuller wrote:
For the numbers, just put 1 in a cell, copy that, then select all the
numbers and paste special: multiply. Excel will convert the text to
numbers and then multiply by 1, thus turning them into numbers. As for
the dates, not sure.
k. moran wrote:
All the numbers are in one column and the dates are all in another column

" wrote:

all of your dates are in one or random columns?

Downloaded numbers from the web are stored as text. No Good
 

One question I have is why is Excel doing this during the download in the
first place? Instead of doing something to the cell is there something I
should do in the VBA for the download??
here is the code

With Selection.QueryTable
.Connection = _
URLStr
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

" wrote:

For the date fields just use the TEXT function: =TEXT(a3,"mm/dd/yyyy").
That will convert your numbers to a usable format. Of course you can
use whatever date format that you'd like, not just the example that I
gave. HTH.

Harold

John Fuller wrote:
For the numbers, just put 1 in a cell, copy that, then select all the
numbers and paste special: multiply. Excel will convert the text to
numbers and then multiply by 1, thus turning them into numbers. As for
the dates, not sure.
k. moran wrote:
All the numbers are in one column and the dates are all in another column

" wrote:

all of your dates are in one or random columns?