rgb192
asked on
but this feature will not be preserved if I save as .csv
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
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
As I posted in your previous question:
[ https://www.experts-exchange.com/questions/28278500/want-leading-0's-in-a-csv-file-not-xls.html?anchorAnswerId=39604348#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 ]
[ https://www.experts-exchange.com/questions/28278500/want-leading-0's-in-a-csv-file-not-xls.html?anchorAnswerId=39604348#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 ]
ASKER
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
how do I get to this screen
[CTRL]+[1] key combination, again, as mentioned in your previous thread:
[ https://www.experts-exchange.com/questions/28278500/want-leading-0's-in-a-csv-file-not-xls.html?anchorAnswerId=39604336#a39604336 ]
If MyField = 123 then this
will result in MyField being "000000123"
MyField = Format(MyField,"000000000")
will result in MyField being "000000123"
ASKER
I press ctrl +1
but I do not know where to copy paste
MyField = Format(MyField,"000000000" )
but I do not know where to copy paste
MyField = Format(MyField,"000000000"
I do not know where to copy pastePlease post the code you use to create the CSV file and I'll modify it.
MyField = Format(MyField,"000000000")
ASKER
there is no code
I can not show you .csv file because of social security numbers
I can not show you .csv file because of social security numbers
ASKER
highlighted all cells in a column
number->custom
copy pasted into textbox
MyField = Format(MyField,"000000000" )
number->custom
copy pasted into textbox
MyField = Format(MyField,"000000000"
there is no codeSorry, my misunderstanding. I thought you were doing this with a macro (which could be done). Let me know if you're interested.
ASKER
yes teach me a macro
I need to change all the fields in 1 column to be 9 digits
I need to change all the fields in 1 column to be 9 digits
Can you post a sample workbook with a few lines of fake data?
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.
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.
ASKER
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
Value in A1, formula in B1
=REPT(0,9-LEN(A1))&A1
which textbox do I type this
do I copy paste
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This answer did not work for me but I feel this answer could work for others. Thanks.
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.
If possible, upload a workbook and I will make the csv file.
MyField = Format(MyField,"000000000"