Force Column to Remain "Text"

[grnzbra]

I have an .xlsm spreadsheet with a column called "Serial Number" which is set as Text. However, when a long number is entered that has no alpha characters in it, it is reduced to a number with an exponent. I don't have control over the data entry in this sheet so I can't force the data entry persons (some of whom may have only a rudimentary understanding of what they are doing) to lead with a apostrophe. Is there any way to force this to remain text and keep all the digits?

 

[SkipVought]

Hi,

FIRST, that is before ANY values are entered into this column, change the NumberFormat to TEXT.

THEN, enter your honkin long Serial Number. It will be stored as character values and not as a number.

Check out:
Faq68-6659
Faq68-7375

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[grnzbra]

Yeah. I know that. Works fine for me here, but what's coming back to me from the field has exponential numbers in fields that claim to be text fields. If I then select the offending field and then the window at the top containing the value, it reverts back to the original long number formatted as text. Not too bad if there are only a hundred or so records, but when one is dealing with a few thousand, eye-balling them is not really an option. I've tried filtering for the e+ portion of the field, but the filter only seems to want to do the comparison on the entire field.

 

[SkipVought]

The exponent is simply a display format.

1) change the NumberFormat for the entire column to TEXT.

2) COPY the column

3) PASTE/SPECIAL--VALUES

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[grnzbra]

Nope. Doesn't work here. Setting the column to TEXT and then typing in the number works great. Copying and pasting, even with PASTE SPECIAL > VALUES still give me the scientific notation. It seems like the PASTE SPECIAL > VALUES just for replacing formulas with the resulting values. Unless there is some obscure setting somewhere in EXCEL that is causing this.

 

[grnzbra]

FOUND THE SOLUTION.

Copy the column and paste special into a TEXT column. Then select the column and select "More Number Formats" from the drop down box. From there select NUMBER and set the number of decimals to zero. The numbers in scientific notation will be converted into the full number in number format. Anything with an alpha character will remain alpha. When this is then selected in SQL Server, the entire column will come in as alpha and can be manipulated as alpha characters.

 

[SkipVought]

Another one-step process is Data > Text to columns.

1) SELECT the data range (column) to convert

2) In Step 3 select TEXT

3) FINISH

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}