Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Problem Converting Numbers that are brought in as text
I have a large Access project that does a lot of export to Excel Wbs and
Shts. Much of the data arrives out of server level DBs. Many columns initially arrive in Excel as though they were quoted text like '3245876 They align left and cannot be used in math procedures. I have a routine that sets those columns as a range and then applies a "Sheet.Range.Value = Sheet.Range.Value. That works (converts data to numerics) on all but one column. The column it does not work on has data that , like those mentioned above, is numeric but comes in as quoted text. The difference in this column is that it has 8 digits like 60113015. When the procedure mentioned above converts this column, Excel apparently believes that the data represents a date and converts it accordingly. Because the resulting date is out of bounds, Excel places ############# in each cell of the column. If I type a 1 in one of the cells, it shows the result as 1/1/1900. Is there a simple work around for this behavior in Excel. Thx for any hints, solutions or sources. Kevin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Problem Converting Numbers that are brought in as text
Is that column formatted as a date before your macro runs?
Maybe change the numberformat for that column before converting it from text. Sheet.Range(..).Numberformat = "0" "Kc-Mass" wrote: I have a large Access project that does a lot of export to Excel Wbs and Shts. Much of the data arrives out of server level DBs. Many columns initially arrive in Excel as though they were quoted text like '3245876 They align left and cannot be used in math procedures. I have a routine that sets those columns as a range and then applies a "Sheet.Range.Value = Sheet.Range.Value. That works (converts data to numerics) on all but one column. The column it does not work on has data that , like those mentioned above, is numeric but comes in as quoted text. The difference in this column is that it has 8 digits like 60113015. When the procedure mentioned above converts this column, Excel apparently believes that the data represents a date and converts it accordingly. Because the resulting date is out of bounds, Excel places ############# in each cell of the column. If I type a 1 in one of the cells, it shows the result as 1/1/1900. Is there a simple work around for this behavior in Excel. Thx for any hints, solutions or sources. Kevin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Problem Converting Numbers that are brought in as text
Thanks for the thought but I tried it and I get the same result as
previously Thx Kevin "JMB" wrote in message ... Is that column formatted as a date before your macro runs? Maybe change the numberformat for that column before converting it from text. Sheet.Range(..).Numberformat = "0" "Kc-Mass" wrote: I have a large Access project that does a lot of export to Excel Wbs and Shts. Much of the data arrives out of server level DBs. Many columns initially arrive in Excel as though they were quoted text like '3245876 They align left and cannot be used in math procedures. I have a routine that sets those columns as a range and then applies a "Sheet.Range.Value = Sheet.Range.Value. That works (converts data to numerics) on all but one column. The column it does not work on has data that , like those mentioned above, is numeric but comes in as quoted text. The difference in this column is that it has 8 digits like 60113015. When the procedure mentioned above converts this column, Excel apparently believes that the data represents a date and converts it accordingly. Because the resulting date is out of bounds, Excel places ############# in each cell of the column. If I type a 1 in one of the cells, it shows the result as 1/1/1900. Is there a simple work around for this behavior in Excel. Thx for any hints, solutions or sources. Kevin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Problem Converting Numbers that are brought in as text
On Sat, 30 Dec 2006 13:59:03 -0500, "Kc-Mass"
<connearney_AT_comcast_PERIOD_net wrote: Thanks for the thought but I tried it and I get the same result as previously Thx Kevin Clearly, given the result when type a "1" in that column, something is formatting the column to DATE. Why not try change the format back to NUMBER (or General) AFTER you import the values and run your SUB on them. --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Problem Converting Numbers that are brought in as text
When changing the format for a column or range from Date to General or
Number, you will need to make the format change and immediately save to file. Then the format change should be effective. "Kc-Mass" wrote: Thanks for the thought but I tried it and I get the same result as previously Thx Kevin "JMB" wrote in message ... Is that column formatted as a date before your macro runs? Maybe change the numberformat for that column before converting it from text. Sheet.Range(..).Numberformat = "0" "Kc-Mass" wrote: I have a large Access project that does a lot of export to Excel Wbs and Shts. Much of the data arrives out of server level DBs. Many columns initially arrive in Excel as though they were quoted text like '3245876 They align left and cannot be used in math procedures. I have a routine that sets those columns as a range and then applies a "Sheet.Range.Value = Sheet.Range.Value. That works (converts data to numerics) on all but one column. The column it does not work on has data that , like those mentioned above, is numeric but comes in as quoted text. The difference in this column is that it has 8 digits like 60113015. When the procedure mentioned above converts this column, Excel apparently believes that the data represents a date and converts it accordingly. Because the resulting date is out of bounds, Excel places ############# in each cell of the column. If I type a 1 in one of the cells, it shows the result as 1/1/1900. Is there a simple work around for this behavior in Excel. Thx for any hints, solutions or sources. Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting text to numbers | Excel Programming | |||
converting numbers to text | New Users to Excel | |||
Converting numbers formatted as text to numbers | Excel Discussion (Misc queries) | |||
Converting text to numbers | Excel Worksheet Functions | |||
converting numbers to text and prefill text field with 0's | Excel Discussion (Misc queries) |