Excel easy format issues for you but not me! 1

[atxross]

Hi all, I seem to be having a problem setting the default formatting option to general for an entire column. Seems that every cell within this particular column wants to cooperate except for some rogue cells. They just keep reverting to a format that I did not set. When I try to set to the correct format, it just reverts back. I'm going silly crazy over here. Even if I delete that entire row, it seems the format just sets itself back. Any help is greatly appreciated.

Thanks to all in advance.

 

[SkipVought]

Hi,

Please post some examples of data that is being changed by format assuming that it is data dependent.

If not, try doing a COMPLETE DELETE --
[tt]
select the column(s)

right click - select Delete
[/tt]
This deletes formulas, values and FORMATS.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[atxross]

I've tried doing the delete column, then I tried deleting the values and the same thing keeps happening. I tried editing the profile excel file[.xlb] and tried reopening excel fresh from the run command: excel.exe -a. Nothing seems to be working


ASSET SERIAL MAC ADDRESS
75502 inm08271mwk 001192cd7b0f
75503 inm08271nwr 001192cd7f99
75504 inm08271n85 001192cd7c99
75505 inm08271nxb 001192cd7fad
75506 inm08271n2z 001192cd7be9
75507 inm08271nks 001192cd7e24
75508 inm0829181h 00119341df9a
75509 inm08291889 1.19E+85
75510 inm08271nlj 001192cd7e3e

 

[SkipVought]

what is the SOURCE data for this line?
[tt]
75509 inm08291889 1.19E+85
[/tt]
Excel has some "smarts" built in. For instance, try entering 1/2.

One half, right? WRONG! Excel ASSUMES that you are entering a date.

You have a value, 100E04. Excel ASSUMES that you are entering a NUMBER in scientific notation, 1.00E+06, which kinda looks like what you are dealing with.

Where is the SOURCE DATA that you are trying to load into Excel?


Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[atxross]

just a mac address like the others shown. That's basically it.

 

[SkipVought]

Then tell me what is happening. I need to know what is changing -- before/after.

The only thing that I can see from the example that you posted is that 1.19E+85 is formatted as numeric (scientific notation). HOW is the data getting into your worksheet? Are you using the Text import wizard or Text to Columns?


Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[atxross]

Ok, sorry bout that, I am entering the data manually; no import or export. I am entering the data the same as you see it in the other cells. When you enter the data into this cell it automatically changes to the scientific even you set the format to general, text etc. I've tried recreating the sheet in a new workbook and it does the same thing.

 

[SkipVought]

Then here's what your're gonna hafta do whenever this happens. PERFIX your entry with an [apostrophy]
[tt]
'1.19E+85
[/tt]
That imposes a TEXT format.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[atxross]

You da man. Thank you sir

 

[pbrodsky]

Formatting the cell as TEXT BEFORE entering the data should preserve the original format. It doesn't work after the value has been entered.