Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Downloaded numbers from the web are stored as text. No Good
all of your dates are in one or random columns?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert numbers stored as text to numbers | Excel Discussion (Misc queries) | |||
Convert numbers stored as text to numbers | Excel Discussion (Misc queries) | |||
VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
How do I convert numbers stored as text with spaces to numbers | Excel Discussion (Misc queries) |