Anyone good with Excel?

[xjrrrdx]

Ill ask if anyone is good with excel because I am having the hardest time with a project at work.

Ill post my issue if anyone is willing Ill save the long post until I can get some assistance.

 

[Eldrich]

Hard to know if 'good' is good enough without knowing the issue.

 

[Village Idiot]

I spend my days staring blankly at lines of code for Access. Excel shouldn't be too difficult.

 

[xjrrrdx]

Okay, here is the issue, lets see if I can translate it to where people can understand my brain lol.

We are working with google earth to create various placemarks via a KML file. We use a KML builder which bases everything off Excel workbooks.

I have 7000 files that need to be parsed sorted and broken into the correct formats for the builder.

What I do is copy the file DIR in dos into a text file, import the text into excel.

I use the built in excel tool to breakup my file name which looks like this (into seperate cells):

YYYYMMDD_1200z_000000N_0000000E_Name

I bring them in as "general" but where the date is it cannot format it because it cant read it as numbers and I just get #### marks.

Thats the main issue, I use a lot of formulas to get my cells to looks like I want. But the builder that chooses all the cells I want seems to view the formulas only and not the output. I am sure its a simple fix but I cant fix it at all!

I wish I could download stuff to my machine but due to the classification of the material there is no internet access.

I hope I made it clearish. Its been driving me nuts trying to figure this damn thing out!!

 

[Village Idiot]

I imported it just fine using the exact line you provide.

How exactly do you import? I opened the text file and when the text import wizard came up, I changed the first column to date: ymd and it imported it as it should have been.

 

[xjrrrdx]

I do it just by using the wizard. I do it by parsing using the slider to cut off where I want the date to end and do YMD but when it imports it, it does not like it. If I go to format the cell it wont let me change any of the data it just stays in the 200800101 format, the only way to get it the format I like is the =A1&"/"&B1&"/"&C1" formula but if I use it then only the formula is seen and of course pops an error up as ivalid entry.

It will split up properly cell by cell like I want but its almost like the data in the cell is just staying text. Not converting like I check the box to do.

Its Office 2007 and there are so many issues with things but its so odd to get this not to work like it should.

I am sure its my issue, but maybe I can pawn it off on the machine lol.

 

[Village Idiot]

Using 2003 at work.

Is there a way to delimit fields by choosing the delimiter? In '03 you can choose what your delimiter is and I used the under score like you have.

Another thing is if it is the delimiter that's giving you problems, you can replace all the _'s with spaces using excel and bat files. It's a pain, but if that's what's causing the error.

Do you currently have any formulas in place in the date column that could be causing a conflict with what you're trying to import?

 

[xjrrrdx]

No the cells are blank. I used delimiters before but it just keeps them as "general" data. I will give it another go tomorrow.

I am getting it done, but I am just using hidden cells to hold my spare data to get what I want.

 

[bikefreax]

Sounds to me like you need to format those specifiec cells. Highlight it and right click and go to format. Then pick either the date or number and make sure it is set up right. Hope this helps.

 

[Aggressor]

Sometimes formatting simply by number won't work. You need to write a macro that will put an apostrophe (') in front of each value. The first apostrophe will not be visible and serves only to tell Excel that the following text is... well, text.

 

[xjrrrdx]

Thanks for all the help it seemed the problem was Excel didnt like it when I used a Fixed Length Delimiter, but id I did a delimiter that seperated by _ then it worked like a charm!