Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Leading Zeros Lost In Csv Format


I have an excel spreadsheet with numbers and the format has to show zeroes at the beginning (e.g. 0011111) It is always going to be a seven digit number. I can format the cells to show the numbers with the leading zeros, but as soon as I save the file as a csv, the leading zeros are lost.

Does anyone know how to the zeros can be retained when the file is saved in csv format?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Number Format To Keep Leading Zeros
I'm using Excel 2007 and am using a look up formula =isna(match( cell, cell range, 0)) to see whether a number in one column occurs in another column.

I have been doing this process successfully for over a year on Excel 2003 and more recently on Excel 2007. This is until today, when it's stopped doing what I expect - could I have changed a setting?

The correct number format for this sheet should be 0000 (so now '67' will appear as '0067' or '290' will appear as '0290' etc.) So I have applied this Custom Format to my columns: '0000'
After applying the Custom Format to the spreadsheet the numbers appear correctly - 560 now appears as 0560 - BUT in the formula bar they still only appear as the original format - so 560 is STILL 560 rather than 0560 as it should be!

As a result of this, my look up is not looking for the number in the new format, it's still looking for the number in its original format - so a look up for 0560 is actually returning results for anything with 560 in it: 560, 5600, 5601, 56000, 56007 etc.
I need my look up to be looking for 0560 NOT 560.

How do I force the numbers to become 4 digits in the formula bar so that my look up for 0560 only finds a match with a cell containing 0560 and not 560, 5600, 5601, 56000, 56007 etc.

View Replies!   View Related
Leading Zeros
I have a CSV file I'm importing into Excell that contains leading zeros in a number field. I cannot get the zeros to display in Excel, but the user of the report needs to see them. Is there a way to dsiplay the leading zeros on a number field?

View Replies!   View Related
Keep Leading Zeros
How do I keep leading zeros in this code? I am incrementing a value with in a string.

TextBox1.value = "PRO 001"

View Replies!   View Related
Retain Leading Zeros
I can't seem to get the Concatenate function to work in my macro when there is a combination of numbers and text. It works fine is there are no alpha characters. I am using this macro to format all of the data in column "A" to have 5 characters and be text. These values will later be used in formulas and Pivot Tables.

Sub Macro2()

Dim sinlen As String
Dim sinformat As Variant
Dim lastcell As Variant

Range("b1").Select
Selection.EntireColumn.Select
Selection.Insert Shift:=xlLeft

Range("a1").Select

Do
ActiveCell.Value = "'" & ActiveCell.Value ......



View Replies!   View Related
COUNTIF And Leading Zeros
I m trying to count strings that have a leading zero with COUNTIF?

Basically, if trying to count "01111", but "01111" does not exist in the data set, and "1111" DOES exist in the data set then COUNTIF returns 1.


View Replies!   View Related
Dropping Leading Zeros
When I save my file as a csv, it drops leading zeros in my zip codes. Formatting the column as text or even zip code only helps when I save it as xls - as soon as I switch it to csv it drops them.

View Replies!   View Related
Leading Zeros In Variable
what i want to do is input a range of numbers into a cell "L12" from 1 to 450
though when in excel i have formated the cell to "number" so when i want number 001 it turns to 1

what i what to do in the macro is after

Dim sNum As Integer

Sheets("FFR").Select

sNum = Range("L12").Value

if the number 1 is in the cell "L12" the number 1 will be in the variable sNum, though i want the number to be 001!

would i have to count the number of numbers in the variable and then say for example if there is only "1" number in the box, use an "add 00" function to get the number in the variable to be 001! I have an idea what to do though no idea where to start!

View Replies!   View Related
Eliminating ^ While Retaining Leading Zeros
Coworker was sent a spreadsheet with a singe column of loan numbers; some start with zeros, some don't. But every single cell ends with a "^". (Shift key and 6)

i.e. 0087459832^
782360134^


View Replies!   View Related
Leading Zeros Are Present In Column
I am looking for a way to ensure that all the leading zeros in one column are present without having to format the column cell be cell. Is this possible?



View Replies!   View Related
Formula To Add Leading Zeros
I need a formula that will make a 4 digit number by adding leading zeros. If the number is already 4 digits then don't add leading zeros, do nothing. all numbers will be 1,2,3,4 digits long.

For Example,

if number is 1 then make it 0001
if number is 11 then make it 0011
if number is 111 then make it 0111
if number is 1111 then leave it 1111

View Replies!   View Related
Formatted Cell With Leading Zeros
I have a whole number in cell C2, but i am formatted it in Cell C3 to always have leading zeros combined with the number in Cell C2, where the result will be 8 digits long. example, my number in cell C2 = 572 but i need it formatted to be 00000572, but if the number was 19 it needs to be formatted to be 00000019 and so on.

View Replies!   View Related
Pad Numbers With Leading Zeros
i have a header row that i want to transfer to a new spreadsheet. here is my exact setup to make it more clear:

Sheet1 - 001 PM Usage$%
Sheet2 - 022 PM Usage$%

i have dozens of these rows, and i want to set up a auto rename for the header row on sheet 2 to rename itself like this:

Dim the value from header row on Sheet 1 (001), and rename the header on Sheet 2 (022), keeping the remaining contents intact (this is important because i want to use the same function for other headers which varies).

what i am doing now is dumping the value of the first cell into a variable, but as it stands it doesnt store 001, it stores 1... stripping the zeros, which makes it more complicated.

i thought perhaps i should dim the first 3 characters in the header as a string, and rename the first 3 characters in header 2, which i think would be the most efficient, but i dont even know if that is possible.

View Replies!   View Related
Retain Leading Zeros In Numbers
I have ID numbers that sometimes starts with one or more zeros and when i try to paste a string containing ID numbers that starts with a zero, excel converts it to a number thereby deleting the leading zero(s). Is there any way to force excel to keep the leading zero when i paste from the clipboard?

Clipboard.SetText strMyString, vbCFText
Set xlSheet = xlBook.Worksheets("Sheet1")
xlSheet.Activate
xlApp. ActiveWorkbook.ActiveSheet. Range("A2").Select
xlApp.ActiveWorkbook.ActiveSheet.Paste

I have tried setting the format for a column as text using xlSheet.Columns("D").NumberFormat = "text" before pasting to try to force excel to keep the leading zero but the ID# comes out unreadable as "############". I have also tried formatting as "General" and custom formatting the column before pasting but have been unsuccessful thus far. I've seen other programs accomplish pasting numbers with leading zeros but how do you do this?

View Replies!   View Related
Web Query Trims Leading Zeros
I have a web query that connects to our financial system, and downloads cost code values.

However, as some of the values have leading zeros, these get dropped off by Excel.

When I look at the data on the web page, a value could 0080, but when it is imported in to Excel using a web query, it show as 80.

BTW, the values can be different lengths, so I can't just pad the front of the number.

I have formatted the cells to text, and I have set the .PreserveFormatting value = True (see code below)....

View Replies!   View Related
Add Missing Leading Zeros To Text
I have a spreadsheet full of site codes which should be the format:
AA1111

or

A1111

Where A = any alpha character & 1 = any numeric character

unfortunately the staff entering the code nearly always miss leading zeros off the numeric part of the site code.

At the moment I manually correct this. Is there a better way to do this?

View Replies!   View Related
Concatenate And Retain Leading, Trailing Zeros
I asked this along time ago and received a worksheet formula which suited my needs then. http://www.excelforum.com/excel-work...trailing+zeros

Now I need this in a macro. Below is my current concatenating code.

View Replies!   View Related
Join Text With Number With Leading Zeros
I'm trying to convert a set of numbers between 1 and 9999 into text strings, with the number at the end as a four digit string, so 1 becomes "COLUMN0001", 123 becomes "COLUMN0123", etc. In excel, it would be easy - I would just use the text function and convert the number to a string and append it to the word COLUMN, but as far as I know, there isn't an equivalent function in excel and running excel functions in VBA is notoriously slow (especially as I will need to do this about 360,000 times).

View Replies!   View Related
Retain Leading Zeros When Copying/Pasting
I have a macros which copies a column of cells to a different spreadsheet and does a few other complex tasks with the data. Some of the data are reference numbers such as 00012345 or 001 which is changed to 12345 and 1 after being copied. What macros could I use to copy this data as is without it being changed?

View Replies!   View Related
Retain Leading Zeros In VBA Array
I am just basically sorting through a huge dataset and grabbing unique codes and storing them in an array for later use. Some of the codes are numeric and some are alphanumeric therefore I am storing them as strings. However, when a code is for example 000578 - once a recall this from the array it has become 578. Is there anyway to preserve the entire code, i.e. make the array recall 000578.

View Replies!   View Related
Web Query Drops Leading Zeros
I have a web query that returns the following two columns of information:

0011111 0-0-1-1-1-1-1
0110111 0-1-1-0-1-1-1
1111100 1-1-1-1-1-0-0

However, the leading zeros in the first column get dropped off in the Excel worksheet, and the information looks like this:

11111 0-0-1-1-1-1-1
110111 0-1-1-0-1-1-1
1111100 1-1-1-1-1-0-0

This means that my lookups later on don't work correctly. I have tried formatting the cells to text, but this hasn't helped. I also can't add the missing zeros back on, as the length can change.

View Replies!   View Related
Add Leading Zeros To Variable Numeric Column
I have a column of about 13,000 10 digit numbers. I need to add four zero to the beginning of each row of numbers so I can make it 14 digit numbers. Is there a way to do this without cutting and pasting four zero's for each row of numbers.

View Replies!   View Related
Formatting Query, Incremental Values With Leading Zeros
I'm tracking weeks by weeknumber in the format YY-WW. At the moment I am entering the weeks manually in a general cell, preceeded by a '. I would prefer to have the sheet work out the weeks for me as we move the sheet every week. I would like to format the initial cell as follows:

09-08 (or '09-08 if I must)
then have the next cell in sequence read 09-09, 09-10, and so on. With the initial week in cell A3, I have tried to accomplish this with:

=LEFT(A3,3)&RIGHT(A3,2)+1

However, this does not work for weeks 01 through 09 because excel kills the leading zero in the second part of the formula, regardless of how I format the cell (format 00-00 does not work.) How can I force Excel it to keep the leading zero in the week number?

View Replies!   View Related
List Of Folder Names Missing Leading Zeros
I have sub folders with names 0001, 003A, 0032 etc in a main folder. I am using the following code to copy these sub folders name to the worksheet column A. This works well except that folder 0001 is copied as 1, 0032 as 32. How to modify the code so that 0001 is displayed in column A instaed of 1.

Private Sub Workbook_Open()
Range("b3:b6500").Clear
Range("c3:c6500").Clear
Dim fs, F, f1, fc, s, i
Range(Cells(3, 1), Cells(6500, 1)).Clear
parentfolder = ThisWorkbook.Path
Set fs = CreateObject("Scripting.FileSystemObject")
Set F = fs.GetFolder(parentfolder)
Set fc = F.SubFolders
For Each f1 In fc
Cells(3 + i, 1) = f1.Name
i = i + 1
Next
End Sub

View Replies!   View Related
Keep/Retain Leading Zeros When Importing Text File
i cant do this from importing the data, so instead i have to try and fix it with code. I get a sheet of data from an external source that removes the 0's in the front of the number. Its only a 4 digit number so 21 would be 0021. Now i have been able to trap the data i think i would need. i created a =len() statement to tell me how many numbers each cell has. here is the code i think how it would work.. i just need a little help with filling in the blanks. I would assume its something to the lines of " 00+string value if it was 2 numbers needed.

B2 has the Len values (4,3,2,1 etc) and A1 is the original string that has the values.
So i want the code to add 1 2 or 3 zeros to the front of the number based on the len value.

Range("B2").Select
While ActiveCell.Value <> ""
ActiveCell.Offset(0, 0).Select
Select Case ActiveCell.Value
Case Is = "3"
ActiveCell.Offset(-1, 0).Select
ActiveCell.Offset(1, 1).Select
Case Is = "2"
ActiveCell.Offset(-1, 0).Select
ActiveCell.Offset(1, 1).Select......................

View Replies!   View Related
Retain Leading Zeros Passing Variable To Cell
I want to know how to index a drop down list to be used for calculations.

Up until now I was using

Range("B1").Select 'the cell where the drop down list is
ActiveCell.Formula = SiteID 'site id

But for whatever reason it trims a trailing zero from SiteID when setting it in the second line. SiteID is a VBA string.

Indexing it would work, as would preserving the zero so help on either one is appreciated. Auto Merged Post Until 24 Hrs Passes;Correction, I lied. I was using this line to set it.

Range("B1").Select 'the cell where the drop down list is
ActiveCell.FormulaR1C1 = SiteID 'site id

View Replies!   View Related
CSV File In Excel Is Trimming Leading 0's
I notice that my csv list of addresses from the northeast area is trimming a lot of zip codes that atart with 0's. this is probably also happening on the address columns for some addresses or box numbers with leading 0s. is there any way to turn this function off?

View Replies!   View Related
Combine/Join Cells & Preserve Leading Zeros
I have 2 columns which contain numbers that have laeding zeros. example:

Column H has 002345 and Column I has 0678. I have tried H1&I1 but the leading zeros are stripped off. Is there a way to combine 2 cells AND preserve the leading zeros in both cells ?

View Replies!   View Related
Lost Format After Pasting
I have two files: let's say File1 and File2.File1 has one column: datas are in a text format.I copy this column. I want to paste it in my second file.File2 is closed.I open it and i paste my column.
Here's my problem. If i let my first file opened, there's no problem(!!): my column is pasted as i want. My datas keep their original format.
But, if i close my first file and after, i open File2 and i paste my column, then my datas lose their original format.My column takes a numeric format.I can see it because my datas are on the left in the column.And i can chek it by a sum: if i do 400+200, for example, the result is 600. In the first case(text format), the result is 0.I don't understand why.If someone can explain me this situation, i'd be happy.I repeat: my second file is closed. I open it after my copy in the first file.


View Replies!   View Related
Date Format Lost With Paste Special-Values
Using 26th January 2007 as an example

I have formatted a cell to show this as dd 26.

When I copy and paste it to another workbork where I needed it shown as a number it appears as 39108.

Is there an easy way to convert the Convert Date Format?

View Replies!   View Related
Leading Zero(s) / Custom Format? ()
I'm having trouble with the leading zeros of my ISBN#s; excel keeps chopping them off. I found a topic that discussed this issue, but it won't work for me.

http://www.mrexcel.com/board2/viewtopic.php?t=75303

I can't format them as text because I'm getting them as an isbn with dashes (ie:978-05689-256-7) and when I remove the dashes (even if its formatted as text) excel changes the number to 9780568.9E+12 (or something like that)

I think the solution would be to create a custom format (ie. 0000000000) to preserve the zeros, but I'm having 2 problems:

1. Since the ISBN#'s have 2 different formats (ISBN-10 & ISBN-13) I need to somehow check if the first 3 numbers are 978 then based on that it should be formatted either as ISBN-10 (0000000000) or ISBN-13 (0000000000000)

2. In the post that I linked to above it said that I won't be able to do a vlookup if its custom formatted. Is there any way around this?

View Replies!   View Related
VBA Format Number With Leading Zero
I have the following script which is working fine execpt for its limited to running the loop 10 times. The macro is looking for files where the file extension could be anything between .P01 and .P99. Currently, the macro looks for file.P01 then file.P02 etc until it gets to file.P09 by increasing the value of 'i'. The trouble I am having is to get the variable 'i' to have a leading zero when the value is less than 10.

Sub rename_print_files()

Dim OldName, NewName
Dim i As Integer
Dim strResult, strOldName, strNewName As String

Range("E14").Activate ' set first file
Application.DisplayAlerts = False

View Replies!   View Related
CSV Date Format
I have a worksheet containing client data which is fed into a third-party, online validation system. The problem is that there are a number of dates contained in the data which the validation system requires to be in the format DD-MMM-YYYY. This is obviously no problem in Excel, however, I then have to convert my worksheet into a CSV file before submitting it.

No matter what I do (and have tried numerous methods), every time I convert to CSV, the dates change to DD-MMM-YY (dropping two Yís). This then fails the validation!! (Note, it is a new validation system on trial so I donít know if anyone has managed anything successfully yet).

What Iím wondering is, does anyone know whether perhaps CSV files do not support DD-MMM-YYYY date format?

View Replies!   View Related
CSV File Format
i have an issue with CSV file format when opening in excel, between Col G and L i have cells which are overlapping to the next cell, not all of them overlap but there is quite a few, is there a way i can fix this, i have 20000 rows of data, i dont want to be going through manually trying to fix.

PFOLIOA/C CODEACCT NAME CURCODE DESCRIPTNNARRATIVE DATE ENTRY DATE CASH VALUE De--------------------------------------------------------------------------------------------------------------------------------------------ABA001AUDCUST NOMINEES AUDINT0005 SALE 39727.08 INTECH BAL GR FD C29-Dec-082-Jan-09-27690.57N

This is what i want to achieve

PFOLIOA/C CODEACCT NAME CURCODE DESCRIPTNNARRATIVE DATE ENTRY DATE CASH VALUE De--------------------------------------------------------------------------------------------------------------------------------------------ABA001AUDCUST NOMINEES AUDINT0005 SALE 39727.08 INTECH BAL GR FD C29-Dec-082-Jan-09-27690.57N

View Replies!   View Related
Decimal Format With No Trailing Zeros
I need to never have a trailing zero be displayed but still have decimals displayed (we can use up to 3 digits for the example) Here's a list of test cases

Value______Display
1.234567___1.235
1.234______1.234
1.23_______1.23
1.230______1.23
1.2________1.2
1.200______1.2
1.0________1
1.000______1
1.020______1.02
1.0002_____1
0__________0
0.001______0.001
0.0001_____0

The only time I want to see a 0 is when the answer is 0 (or rounded to 0) or when 0 is required in the middle. This is for patient safety if you are curious. 2.0 when printed out or written by hand can be mistakenly read as "20" in the heat of things, thus 2.0 should be written/printed as "2" (no decimal on the end) not as "2." even, just "2"

View Replies!   View Related
PasteSpecial Format CSV From Recordset
Currently, my code involves making a connection to a db then run queries. Results from the queries goes to recordset & from the recordset copy to specified range in excel worksheet. I am trying to do an automation process.

The problem is that once the record is pasted in excel worksheet, the date column is not being recognised as date therefore excel function (vlookup) is not giving me the results in my report.

When i do a manual PasteSpecail as CSV into the worksheet from the query result, my report gets populated with data which is correct.

i've tried doing a pastespecial format:="CSV" but it doesnt work.

Is there any codes that i can use to copy from the recordset as a CSV format pastespecial??

Or any other ways that can be done?

View Replies!   View Related
Data Moves To The Right In CSV Format
i have csv data which has splits cells due to commas between numbers, and then moves data further to the right, is there a way to fix this, so for example this is what some cells look like

31874.90 JPMORGAN

but need to be like this

31 874.90 JPMORGAN

View Replies!   View Related
Referencing To CSV File In Strange Format
I am working on an excel dashboard to display life information about energy consumption.

there is currently a bespoke syatem which does this but we need the dashboard to be changed so the job has fallen to me.

Energy usage across the site is recorded in a spreadsheet (automatically) every half an hour and is saved in a folder named CSV data, however the files are saved in a format which i assume is something for the current package: *.ew7

I can open and view the data by clicking "open as" and selecting excel.

I can reference to this data from another excel spreadsheet (dashboard) whilst they are both open it will update.

However when i close all spreadsheets and open just the dashboard it tells me that the links cannot be updated.

I need a solution or a way around this so i can reference from one workbook to another.

Has anyone had any experience of this kind of thing? can anyone offer a humble analyst a bit of advice?

I was not able to attach the file here (as its in the ew7 format) but i have uploaded it to rapidshare for anyone who wants to take a look...

View Replies!   View Related
Custom Date Format To Be Retained In .csv Also
i have the required date as part of a string, which i need to pull out and then format it as "yyyy-mm-dd hh:mm:ss" and put it in another file which will be saved as .csv

Here is the code i am trying to modify without much success so far

Sub dateFormat()

Dim dtMydate As Date
Dim sMydate As String
Dim strWhen As String

strWhen = "As on 13-MAR-2007 14:45:59 Hours IST" ' the starting string
'strWhen = Cells. Find("As on")

'take out the date portion from strWhen
strWhen = Mid(strWhen, InStr(strWhen, "-") - 2, (InStr(strWhen, "Hours") - 1) - (InStr(strWhen, "-") - 2))

dtMydate = CDate(strWhen)
Debug.Print dtMydate 'returned value is 13/03/2007 2:45:59 PM

dtMydate = Format(dtMydate, "yyyy-mm-dd hh:mm:ss")
Debug.Print dtMydate 'returned value is again 13/03/2007 2:45:59 PM...

View Replies!   View Related
Non US Date Format For Dates In CSV File
I am using a CSV that has the date in Column A in the format of DD/MM/YYYY. When I open this CSV using a VBA comand the Format of the Cells in this col are then formated as either Date or General. The general format happends when the date cant be turned into US format and when it can then the date is turned US and displayd as such.

SO
14- Sep = 14/09/2007 as General
10- Sep = 09/10/2007 as Date

Im in Australia so the correct format is DD/MM/YYYY. Microsoft has issued a fix for this using Local:=True in the file open comand which is all good and well as it works unless you have office 2000 when it crashes. What would be the best way of fixing this, so that the Dates are all correct or the col is all the same format?

View Replies!   View Related
Date Format Changes When Creating Text File From CSV
What I have is a double quoted, comma delimited text file that is created from an external application, every now and again we need to open the file to make corrections and this is easier if we open it via Excel using the delimiter options, the problem is when saving the file as a csv it removes the double quotes, however I have found some vba coding that adds the double quotes back, which works great, but it changes the format of the date fields from what the original file had from 09-Aug-2009 to 09/08/2009.

I suspect this may be because the file is opened before the double quotes are added and excel changes the format.

Is there any code I can add that will stop the date format from changing ...

View Replies!   View Related
Changing A Csv Format To Xls And Saving/closing
I am trying to write a macro/program that will save a csv activeworkbook in xls and close it.

The workbook will always be a different name.

The files do not have an extension upon download and formatting.
ie stm0107 (Then they need to be saved as xls. Which is done manually at this time.)

Below are some trial and error attemtps. Thank you for your suggestions.

Sub CloseWorkbook()
Dim Filename As String
'Filename = Workbook.Name
MsgBox (ActiveWorkbook.Name)
Filename = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename = ActiveWorkbook.Name(".xls")

'Filechange = Replace(Filename, "", "xls")
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
ActiveWorkbook.Close Saved = True
End Sub

View Replies!   View Related
Defining Field Format For Data Import From CSV
on data import from CSV file. The file is formated in a sligthly strange way:

"London","182.56","12/12/2007"
"Paris","133.41","27/12/2007"

I get into problem when importing using default CSV settings as numbers and date are understood like text. I need to replace "." with "," to get to number format. Used following Range("J5:K9999").Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

However I get values multiplied by 100 for some reason.

Converting date format gets eeven more complicated.
So I decided I can not use standard CSV import and need to define separate data source for each raw file This is not what I wanted, since I do import many files (10 at this point in time, but it will get growing). However, I would be ready to do that if there is no other option. So I go to "Create New Data Source" and under "Connect" I get to another input box called "ODBC Text Setup" where I select "Options" to define customer format. However, after selecting source file, Excel is not able to interprate fields. Tried clicking "Guess" button, no luck there. Thought it is because fo those "" before and after field value. So, just for a sake of experiment I opened file in text editor and removed those ". Still, can not read the file. The error message says "Text file specification field separator matches decimal separator or text delimiter. Guess failed"

I am out of ideas how to get this file properly imported. Hope there is a way to do that either by defining field formats while importing (perhaps that can be done via SQL, but I am not confident with it) or writing some code to change format after importing from text to Number and Date...

View Replies!   View Related
Copy-paste From Csv To Xls Macro > Wrong Date Format
I'm exporting a csv file from a membership database with the attendance of members. With a macro i open the file and copy the contend to excel. With the macro the column for the date has *randomly* wrong formats. Example the date in column B:

Aasia Tallah19-03-2009 19:33
Aasia Tallah17/03/09 7:49 pm
Aasia Tallah15/03/09 7:53 pm
Aasia Tallah14/03/09 6:34 pm
Aasia Tallah03-12-2009 20:05
Aasia Tallah03-11-2009 19:56
Aasia Tallah03-10-2009 19:54

I try to change the format of the column, but that does not work. Rows 2,3 and 4 seem to be text but they are not. If i access the edit bar for example the 17th of march and store without changing anything, it changes to the format of the first row. That is how i want it. If i copy the column by hand, every thing is fine.

All of the following i tryed:
- format date column of the csv before copying... failed
- copy special with only pasting values... failed
- preformat the column i paste the date to... failed
- delete the sheet i paste to and create a new one... failed
- copy the macro to a new workbook... failed

View Replies!   View Related
Suppress Zeros In A Pivot Table (search Out Any Zeros And Replace With A Blank Cell)
with the data in the attached sheet, I create several different pivot tables that need show the count of the information in the columns M:DU. My issue is that the data is sent to me from a third party and the columns contain zeros that cause the counts to inflate.

What I would like to be able to do is run a macro that will search out any zeros in M:DU and replace them with a blank cell.

Unfortunately the number of rows increases with every monthly reporting cycle so the macro would need to be able to accommodate for that.

View Replies!   View Related
How To Save Csv As Excel "FileFormat:=xlExcel8" Format?
when i open csv file i run this macro and i want to save the file as .xls file

my macro is : ...

View Replies!   View Related
Csv - Text To Columns Change Back To Csv
I have a csv file which I opened in excel file then i converted the csv to excel columns format using the text to columns delimited.

Now I need to change it back to its original form which is the csv.

View Replies!   View Related
Imaginary Zeros In Formulas Vs. Real Zeros
Iíve created a formula for this statistic and Iím happy with the results. Because Iím working with formulas, my only problem is the unwanted zeros. How do I hide zeros that show up automatically (i.e. #3 [blank] and Nov 09-June 10)? I can hide the numbers, but if I enter a zero to one of my future statistics it will not appear and I donít want that to happen. Is there a way to hide those automatic zeros without affecting my real zeros?

Vendorís Name

Jul 09

Aug 09

Sep 09

Oct 09

Nov 09

Dec 09

Jan 10

Feb 10

Mar 10

Apr 10

May 10

June 10

1

Vendor1

20

5

15

3

0

0

0

0

View Replies!   View Related
Lost Formatting
I have a workbook that is used by others and has been working reasonably well for several years. The users are youngsters who tend to play with it when a bit bored but I usually manage to put things right. They seem to have done something special this time.

The formatting for dates, time and money has gone and I cannot correct it. I have tried the usual formatting of cells, painting formats from other sheets, copying and pasting formats all to no avail. The whole sheet is effected so that new entries appear the same way although the correct format appears in the formula bar.

I don't know if this is relevant but the sheet relies on VBA form inputs. The code is password protected and only I know the password.

The PC is not connect to the internet or a network so I have ruled out a virus. (As far as I know, no floppies or USB sticks have been used on it either.)

View Replies!   View Related
Lost The Menus
Having recently started to get involved in Visual Basic I have managed to write some code that has removed all of my menus except for File and Help. In a blind panic I deleted what I think was the offending VB code in a vain attempt to return the menus. This didn't work. I am now stumped.

Also, I managed to change the way the Cell selection works when I press Return. When I press Return, Excel now selects the cell to the right of the one I was in rather than the one below it. Is there a way I can change this back?

View Replies!   View Related
Formula Lost
I have been chasing this little devil for some time now and I'm fed up!!

I put a formula in cells formula bar and whenever it decides by itself poof its gone!!!

example formula would be say (in formula bar)

=SUMPRODUCT(--($A$5:$A$200>(h4/24)),--($A$5:$A$200<(h5/24)))

in say cell g31 with answer in cell 31.

Now it just dawn on me, this is an array correct so ctrol,shift, enter may be needed?? If so why do other cells with same idea work?

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved