Excel Auto Fill Issue

[KuraiChikara]

Hello,
I have an issue with Excel's auto fill.

Problem:
In Column A1, say you have random numbers, 424, 80, 322, 71 etc, but this random number goes down as far as 500 rows.

Let's say from Column B-I you have random information as well.

In the next free column, say, Column J, what you want to auto fill is the word "agent" next to the random number, IE Agent 424, Agent 80, Agent 322, Agent 71 etc.

I begin adding the information:

In J1 I type "Agent 424", J2 I begin to type "Agent" but excel wants to auto fill with the previous entry (Agent 424) but I add the next number "Agent 80"

By the third row, Excel then sees what I'm doing, it looks into the rest of column A and will opaquely auto fill the remaining information all the way down in Column J, I hit enter, then the information populates and is solid.

Here is the problem:
For a few rows THIS DOES WORK, but by maybe the 20th or so row down in Column J the format gets all wacky, instead of "Agent and number" the word agent is all screwy, it will read "Pmajh, Asastand, Pmwsr, etc" I don't know why I'm losing the "Agent" format in the auto fill.

I assume it's looking into the rest of the data between Column A and Column J but I don't know why or how to stop it.

I first thought it was a TEXT TO COLUMNS>>DELIMITED>>SPACE issue, but that's not the case.

 

[KuraiChikara]

I fixed my own issue. In "FORMAT CELLS" in number, under "TYPE" I put in the format "Agent"#.

I then put in the first cell with that format and copy the format down. The first cell turns into "Agent 424."

I then copy all numbers from A2-A500, right click, paste values in J2-J500, when the numbers are placed, they are auto formatted to what I want and the name change of "Agent" does not occur.

 

[KuraiChikara]

To add to this, what also works is CONCATENATE Agents to the number, this will allow you to keep the TEXT and the NUMBER for whatever reason you may need (specific formatting for formulas, using Delimited>>Space in Text to Columns etc) the example I gave above technically removes the text and the only value will be the number so you couldnt delimit or do other tasks requiring the text.