I'm trying to automate some data entry tasks in excel. I take information from email, or PDF, and paste it into excel and then run a macro on it.
The problem is that one of the columns of data has information like 05-1, 05-NC1, 06-12
The ones with letters move fine, but whenever there is only a number, a hyphen, and another number, excel automatically changes it to 5-May, 12-Jun, etc.

How can I stop excel from doing this?
If it helps, I'm using "text to columns" most of the time and I have already tried preformatting the empty columns to text before pasting them.

Thanks if you can help.

2/27/2007 9:56:35 PM

i had a similar situation. it should work for you since you're using a macro anyway. this is how i solved it:

while you have more columns
if the current column's text has a hyphen, format column to general.
continue


....or if you don't care about the format of the other columns, just add a mass format to all columns as general.


i can give you the source code for this tomorrow if you don't figure it out by then

2/27/2007 10:07:01 PM

If you're running a macro on it anyway, why not just change the format with the macro?

2/27/2007 10:09:18 PM

Well, the main problem is that it does it on the very first paste.

So for instance, I get text than paste into one cell like this:

ABST 05-1 400 BofA

Then I text to columns it to separate the various pieces into their own cells. What I end up with is:
ABST 1-May 400 BofA

If I change the format of that cell with 1-May, I get the numerical date of 30422 or some crap like that. Thus, I really don't know how to use the macro to fix it unless I have it change the format first and then find and replace 30422 with 05-1 (And another 364 find and replace dialogues)

[Edited on February 27, 2007 at 10:12 PM. Reason : s]

2/27/2007 10:12:03 PM

before you paste the data in, go to the column where the hypens may be, select the column and go to Format and select "Text" as the cell format. Then you should be able to paste it in and it will remain as a text/string value.

Unfornately, if you paste first, then change it to text, the original data will be lost and it will just turn "5-May" into a string, which is obviously no good

2/27/2007 10:18:48 PM

Right, I tried that.
Take this Text "ABST 05-1 400 BofA"
and paste it into a cell in excel. This replicates when I grab the text from PDF or email.
It should all go into 1 cell. Now format ALL columns into text.
Then use Data\Text to Columns to expand each spaced chunk of that original cell into its own cell.

It automatically changes the 05-1 to 1-May, regardless of the fact that I told it to format as text.

You see the problem?

2/27/2007 10:23:42 PM

ok, yeah.

but when you do Data > Text to Columns, it brings up a wizard, right? You go through and select "space delimited" or whatever. On the next step after you select the delimiter, it shows you a preview of what all the new columns will look like. On that preview, you can highlight a column then select the appropriate format from the radio buttons on the upper right.

2/27/2007 10:44:40 PM

Agent lion has the right idea but you dont even have to do that.
just go to
data>text to columns>fixed width>dont change the column spacing or anything> the screen you are on now you can select each individual column. so when you change it to text it is automatically selected the first column so only that one is text and the 05-1 is still general format so just click on that column in the diagram and then select text. viola it stays 05-1 and not a date. its just that you mistook the text to change all the columns when it only changes the selected column.

2/27/2007 11:28:03 PM

^ i'm pretty sure that's the same way I was saying.

2/27/2007 11:53:02 PM

Thanks guys. That helps. I had to add one step, which was to find and replace 2005 with 05 before I do the text to columns, because some of the data comes in as 2005-5, etc. when it has to be added to the database as 05-5, etc.

But, you didn't know that, so you couldn't anticipate it.

Thanks a lot.

2/28/2007 7:35:53 AM