UK Bank Sort codes - Export from xls to csv

**David_S_Walker** · 03-20-2009, 05:36 PM

Hi,

New boy here!

My partner works in payroll in the UK and she regularly needs to export from client received .xls files which contain UK Bank sort codes in the format NN-NN-NN (eg 11-12-46 or similar) Despite formating these fields as text when she converts (saves as) to .csv (required for input to her payroll software) the field is displayed as a date in some cases. So for example 20-42-00 would be OK when converted to .csv but 01-11-56 would be displayed as a date!

I think it must be in the number formatting but I can't work it out.

Has anyone got any ideas for this and apologies if this has been asked before

Best regards,

David

**ConneXionLost** · 03-20-2009, 05:45 PM

Have you tried placing a single apostrophe before the bank code like this:

'NN-NN-NN

or

'11-12-46

Cheers,

**David_S_Walker** · 03-20-2009, 05:57 PM

Hi,

Thanks for that - Yes we have and it works in the .csv file which is produced from the .xls but the files are sometimes huge with hundreds of sort codes in them. So the question is now how would you apply to a column of data the ' character universally to preserve the format? If I did a say a replace 1 with '1 it would change all the 1 characters in the sort code. Ideally we would like to do this in the original .xls file which is supplied by clients before saving/converting to a .csv file.

Just getting a bit confused

Thanks for taking the time to reply though

Best regards,

David