Strange unseen characters in front of numbers

Hi, welcome to the forum

You have over 3000 rows of data there, can you point me to some that have that problem? (Im not going to search them all)

Not seeing it in the uploaded file.

However, the values in column A in that file are stored as numbers, therefore you will not find a value of 066216666 as the zero is not counted.

If you really are seeing a character before your number, Copy and paste it into Find/Replace window, replacing it with nothing. That should remove it from all your cells.‏

It looks like it's designating those numbers as text. It is not seen on the values that do not start with 0 (i.e. actual numbers).

Is the issue with with sql or with Excel? I think if you use the formula that Hillsy suggested, it will take care of your Excel issues, everything will be text and everything will be 9 characters long.

Okay, if you don't want them to be Null, then we need to do the opposite.
You need to convert all your Excel values to numbers before you import them into sql

One way, In a blank cell enter 1. Then Copy it
Select your range of numbers and Paste Special> Multiply
That will convert all the text to numbers

Did that work?

Won't let me post formula. What's up?

This might also be of interest. It takes the value in column A and changes it to text padded to 9 characters if necessary by adding leading 0s.

Formula:

Please Login or Register  to view this content.

All I noticed is that some cells have a leading and an ending character hex 00FE that should not be there.
No idea how to clean your cells with an Excel formula or with VBA but as a work-around you could:
1) copy the column to a sheet in a new file
2) save the new file as .CSV
3) open this new .CSV file with Notepad
4) from menu substitute all the " ? " characters with " leave empty " to get rid of the extra characters that now show as a question mark
5) save the file
6) open the new .CSV with Excel
7) copy the cleaned column back to the origin (copy of )

REPLACE works too, but Forum won't take my post with the formula.

I've never tried that before.

@FlameRetired, next time reduce the dimension of the Excel window, center the cells you need to show and then press Alt+Stamp (instead of only Stamp) to copy.

It seems to me that your formula, as many other attempts I have done, when you copy the column and use Paste Special/Value to get rid of the formula and have only the resulting numbers, nothing changes.

Originally Posted by rollis13

@FlameRetired, next time reduce the dimension of the Excel window, center the cells you need to show and then press Alt+Stamp (instead of only Stamp) to copy.

It seems to me that your formula, as many other attempts I have done, when you copy the column and use Paste Special/Value to get rid of the formula and have only the resulting numbers, nothing changes.

Thanks for the Alt+Stamp tip. I always wondered how posters did that! LOL

The "center the cells" part 'threw' me. My intent was to show the formula in the formula bar. I should have been clearer about that. My apologies.

I did a copy / paste values of the cells containing my formula and the characters were still removed as intended.

What am I missing?

The "center the cells" part 'threw' me.

It's ok, when you reduce the size of the window it becomes hard to match formula bar and cells

What am I missing?

Tested your formula again, my mistake (did a copy/paste of the wrong cells). But, your formula has to be used only on the offending cells, so sorting is needed to find them all.

It looks to me that the "special characters" only occur when you import numbers to sql as text. Therefore, to remove the characters, he needs to convert everything to numbers in Excel, not the other way around. If he wants it visually to look like 9 digits with leading zeroes, then he can do a custom format of 000000000.

The question then is, will the number format be imported as shown in Excel. It has been a very long time since I imported from Excel into Sql but at that time the formatting was stripped when imported. Of course in the time that has passed, a lot has probably changed in that are.

I don't believe that the formatting will be exported. It comes down to which is more important to the user, the ability to use these values as numbers or the way they look.

@ChemistB
I agree, with your question about what is important.

If I remember correctly, SQL is able to format output as required as long as the data is consistent (all numbers in this case) including padding the output to a number of characters.