Excel converting imported values to dates

[fiordhraoi]

Hi all,

I'm working with an in-house programmed (though the programmer is long gone at this point) network management tool that can export data to Excel. One of the reports it generates lists the recent errors on a router/circuit. It pulls up a new instance of Excel to do so. Most of the time, it ends up exporting data like:

Incident code, RouterName, Location, IP address, Max Load, etc

Normally that's not a problem. However, the customer that I'm working with now has their routers named with a 3 number string. So for example, the router in Region 3, Division 8, Office 18 is named 3-8-18.

Unfortunately, upon import, excel decides that this MUST be a date, and converts the value to that absolute date number it likes to use.

If it were a small number, this wouldn't be a huge problem, I could correct it manually. Unfortunately, I'm looking at about 40k records at the moment. In order to do the data analysis properly, I wanted to do a Text to Columns, separate the router name into 3 separate numbers, and thus I'd be able to filter by division, region, etc. If I do a text to columns now, it "kind of" works, but I end up with 1-3-00, 8, 2018.

Is there any way to stop excel from interpreting it as a date? I can't alter the values, which means I can't put a single quote in front. And I can't pre-format the cells, because the program that exports it actually opens up a new instance of Excel to dump the data.

Thanks guys!

 

[SkipVought]

Hi,

Can't you specify in the IMPORT spec, that this field is TEXT?

Data/Get External Data/Import Text....

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[fiordhraoi]

Unfortunately, no. I'm not using the "data/import external data" thing to import the data. I click a command button in a third-party program, and it exports the data directly to excel, pulling up a new instance of Excel as it does so. There is no intermediary file.

 

[straybullet]

If tried though, would Import to Excel work as desired rather than using Export from this other program?

There are many selections in the datatype dropdown when setting up the import - perhaps one would work?

Let them hate - so long as they fear... Lucius Accius

 

[SkipVought]

Well then the problem is with your 3rd party program!

But all is not lost necessarily. Check out Why do Dates and Times seem to be so much trouble? faq68-5827 and understand it completely.

Excel interprets 3-8-18 as a date -- 3/8/2018 on my system with my regional date/time settings.

18 is interpreted as 2018. 29 is interpreted as 2029, while 30 is interpreted as 1930.

But basically, your 3 "numbers" for a date in A1, are
[tt]
=TEXT(A1,"YY")
=TEXT(A1,"M")
=TEXT(A1,"D")
[/tt]




Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}