• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 45
  • Last Modified:

formatting cell to 10 digits in excel

I have a column that is a text .  It needs preceeding zero's for identification.
when i copy this data out of a sqy query result and paste it in to excel, it will take out the leading zeros.
I can custom format the column with 0000000000  (ten zeros)  and it works fine visibly.  when i then take the newly formatted column
of data and paste it to another column, it will revert back and take out the leading zeros.  This is paste values.
I can also paste with formatting and that seems to work fine.

My problem is this... when i import that data back into sql, it reverts back to truncating the preceeding zeros.

there are 4 columns that show my process

i can't use the & function or concatenate function to add just one zero because some of the accounts have 2 preceeding zeros...
ten-digit-help.xlsx
0
jamesmetcalf74
Asked:
jamesmetcalf74
  • 3
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>it will take out the leading zeros.
That's a very common issue, as Excel interprets the data type as numeric and formats accordingly, and all numeric data types do not support leading zeros.

Couple of options:
  • Add a single tick mark ' to the left of any export to Excel, which will interpret it as text and not numeric.  Then in any import from Excel to SQL Server remove the tick mark.
  • Edit your 'import that data back into sql' process to specifically ensure the leading zeros are there plus insert into a char/varchar column.
0
 
jamesmetcalf74Author Commented:
trying to add a single quote to output of sql query and am unable to.... i can add just about anything else but am having trouble with single quote
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Show us the T-SQL, as adding a quote mark within a string usually means two quote marks, For example..
SELECT 'My name is Radar O''Reilly.  Choppers!!!'

Open in new window

0
 
jamesmetcalf74Author Commented:
no success.  i have tried with and without parenthesis and with and without the '+'
USE Test
 GO
SELECT  ('' + f.designatornumber)
0
 
jamesmetcalf74Author Commented:
just got it
thanks ST. Jimbo
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now