MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Cleaning an import into Excel


Posted by David on December 12, 2001 8:26 PM

I need to import information from a contact manager that will only export into a tab delimited format. Each of the populated fields in the Excel spreadsheet now have a rectangular character following the relevant information of every cell.

I have not managed to duplicate the character, and when I cut and paste the contents of one of these cells it disappears (it does not transfer onto a clipboard)

I need an automated way to erase these rectangular characters from each one of thousands of cells. Can anyone help??


Posted by Bariloche on December 12, 2001 11:17 PM

David,

See if this works:

Insert a new sheet (Sheet2), assuming you have data in Sheet1, cell A1, type the following in Sheet2, cell A1:

=LEFT(Sheet1!A1,LEN(Sheet1!A1)-1)


If it does then just copy that formula to the same range on sheet 2 as your data on sheet 1, copy the sheet 2 range, go back to sheet 1 and paste values.


Bar