involuntary format changes

[Guest]

I have prepared a file of bank payments for electronic payments. The fields
(column heads) are

sort code account name account number amount

The final output file has to be in CSV format to be read by the bank’s
computer.

All cell formats are “general†or “textâ€.


Sort codes are 6 digit numbers shown as follows: 63-45-82.

That particular series retains its “formatâ€. However having saved the csv
file and closed it the format changes. When next opened and in those cases
where

1) the first two digits are less than or equal to 31

and

2) the next two digits are between 1 and 12

the format changes to “date†and a date shown.

So: 63-45-82 will retain the format “general†and appear as shown, but


23-10-76 will initially appear as such but on re-opening will appear as
23/10/1976

If I change the format to “general†it divides 23 by 10/1976.

The only way I can get round this is to adjust each offending cell manually
by introducing a ‘. Unfortunately I have over 600 possible instances so this
becomes laborious. I have tried amending the original data entry so that the
relevant field is preceded by a ‘ but this doesn’t work and I’m not sure
that it could be read by the bank’s computer anyway.

Any further ideas please.
Thanks
Dave.

 

[Guest]

Dave,

The problem with CSV is that it doesn't always store the formatting you want.
You could try this little bit of VBA which will put apostrophes in front of
each cell in the current selection - select all the cells you want to modify
and run the macro.
Not ideal, but I hope it helps.

Pete

Sub AddApostropheToRange()
Dim CellToModify As Object
For Each CellToModify In Selection
CellToModify.Formula = "'" & CellToModify.Formula
Next CellToModify
End Sub

 

[Guest]

Hi Pete,

Thanks just gonna try it

Regards

Dave