?
Solved

but this feature will not be preserved if I save as .csv

Posted on 2013-10-27
17
Medium Priority
?
219 Views
Last Modified: 2013-11-11
want all the fields in a column to be 9 digits

some numbers are
67
want

00000000000067

tried column change in microsoft excel 2007

but will not save because I need to convert to .csv




Click the [Number] tab if not already shown, & select the "Custom" Category.

For 9 digits, enter a Type of:
000000000

Confirm with the [OK] button.

PS. I note that your question asks for "9 digits" but you then state:
00000000000067

(14 digits)

If you do want 14 digits, enter a Type of:
00000000000000


but this feature will not be preserved if I save as .csv
0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +1
17 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39604352
Format it like this and then save it.
MyField = Format(MyField,"000000000")
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39604353
As I posted in your previous question:

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28278500.html#a39604348 ]

In order to maintain leading zero(e)s within Comma Separated Value [CSV] format files, the respective columns (fields) will be required to be enclosed within quotes.

For example...
---
"00000000000067", "second column row 1"
"00000000000123", "second column row 2"
"00000000004567", "second column row 3"
---

If you wish to create CSV files with numeric fields in this manner, then you will need a bespoke file creation routine such as this:

[ http://www.mcgimpsey.com/excel/textfiles.html#fixedfield ]
0
 

Author Comment

by:rgb192
ID: 39604359
Format it like this and then save it.
MyField = Format(MyField,"000000000")

how do I get to this screen



fanpages
I type
format
000000000
and this works

but when I save as .csv
the feature will not be preserved

"000000000"
does literally 9 0s for every field in the column

I would show screenshot but these are social security numbers


problem is some social security numbers  begin with a leading 0 which have been dropped off

I need to upload to a computer program as a csv not xls or xlsx
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39604361
how do I get to this screen

[CTRL]+[1] key combination, again, as mentioned in your previous thread:

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28278500.html#a39604336 ]
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39604364
If MyField = 123 then this

MyField = Format(MyField,"000000000")

Open in new window


will result in MyField being "000000123"
0
 

Author Comment

by:rgb192
ID: 39604382
I press ctrl +1
but I do not know where to copy paste
MyField = Format(MyField,"000000000")
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39604386
I do not know where to copy paste
MyField = Format(MyField,"000000000")
Please post the code you use to create the CSV file and I'll modify it.
0
 

Author Comment

by:rgb192
ID: 39604391
there is no code

I can not show you .csv file because of social security numbers
0
 

Author Comment

by:rgb192
ID: 39604397
highlighted all cells in a column
number->custom
copy pasted into textbox
MyField = Format(MyField,"000000000")
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39604423
there is no code
Sorry, my misunderstanding. I thought you were doing this with a macro (which could be done). Let me know if you're interested.
0
 

Author Comment

by:rgb192
ID: 39604428
yes teach me a macro
I need to change all the fields in 1 column to be 9 digits
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39604460
Can you post a sample workbook with a few lines of fake data?
0
 
LVL 23

Expert Comment

by:Ejgil Hedegaard
ID: 39604491
It don't require a macro, you can do it with a formula.
Value in A1, formula in B1
=REPT(0,9-LEN(A1))&A1
Then 67 will be 000000067 in B1, 9 characters with leading zeros.
Copy formula down and copy all values in column B to a new workbook or another sheet. Insert as values.
Then save the sheet as csv , and the format 000000067 will be preserved. in the csv file.
You can open in Notebook to check.
If you open from windows, Excel convert to 67, so use data import, and set the field to text and it will be 000000067.
0
 

Author Comment

by:rgb192
ID: 39604534
It don't require a macro, you can do it with a formula.
Value in A1, formula in B1
=REPT(0,9-LEN(A1))&A1


which textbox do I type this

do I copy paste
0
 
LVL 23

Accepted Solution

by:
Ejgil Hedegaard earned 2000 total points
ID: 39606048
No textbox, type the formula in the cell, and then you copy paste.

With the values in column A, starting in A1, then insert the formula in cell B1.
Copy the formula in cell B1 down to the last value in column A.
The easy way is to double-click the black spot in the lower right corner of the cell marker.
Open a new workbook to have a place for the result.
Go back to the workbook with the values, mark all cells from B1 down to last value, and copy.
Switch to the new workbook, with the cell marker in A1, right-click on A1, select Paste special, and select Values in the dialog, then Ok.
Go to Save As and select Other Formats.
Insert a Filename, and in the Filetype box below the Filename box, click on the arrow at the right, scroll down to find CSV, and select that.
You get 2 warnings, accept both.
Close the file.
0
 

Author Closing Comment

by:rgb192
ID: 39639664
This answer did not work for me but I feel this answer could work for others.  Thanks.
0
 
LVL 23

Expert Comment

by:Ejgil Hedegaard
ID: 39640034
Strange it did not work for you, I have done it several times.
If possible, upload a workbook and I will make the csv file.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question