Thread: Excel auto-formatting fields

Hi All,

I do a lot of work with cleaning up fixed width text files in Excel. The files include a lot of rubbish data (for example the files include pipes separating the columns) which I need to remove to get a clean csv file.

I always import the data as text which works fine until I start cleaning it up. If I do a find/replace to remove the unwanted characters Excel then sees it as a number and auto-formats the fields to a number or date which can mean my data gets messed up (lose leading zeros etc.). If the character I am removing is at the front of the field and it is a number then I can replace it with a ' which seems to work OK, but sometimes these columns include rows with both numbers and text or the character is at the end of the field.

Is there anyway that I can stop Excel changing the field type from text in these situations?