Delimiting removes data. Formatting issue?

[BPSJACK]

Hi,

I'm having a bit of a problem I can't solve. I have a macro that imports data to an excel file, I need to the delimit the data once it's in the workbook.

I can see the data there, but when I try to delimit it it comes out blank. Because the data is primarily exported from a third party software as an .xls I'm wondering whether it may be formatted in a funny way the Excel 2013 can't read it.

I've attached an example. Anyone come across this before? Any help would be very much appreciated.

[Norie]

Is the file you've uploaded before/after?

Whichever it is how are you delimiting?

PS The dates seem to be preceded by a ' and a blank line, or linefeed character.

[BPSJACK]

The file I've uploaded is before attempting to delimit. I'm trying to delimit column D with " : " character.

I've tried all sorts of trim, clean and substitutes but i cant seem to remove the line.

[BPSJACK]

Appears I've solved my own problem.

Inserted the following function:

Please Login or Register  to view this content.

And the just applied it to the cells. Copied, pasted the values and deleted the originals. And then it could be delimited.

[Norie]

Try using Fixed width with a break before/after the colon and after the split unwrap the columns.

[newdoverman]

Enter this formula in E1 and copy across to fill 4 columns and then copy down the length of your data to remove non-printing characters whose ASCII codes are from 0 to 31.

Please Login or Register  to view this content.

Select the columns with numbers and click in the yellow diamond and click on Convert to number. This will convert all text numbers to real numbers.

Select all of your new data and copy then Paste (in place...don't change the selection) Values. This will get rid of the formulae and leave you with values only.

Column H of you new values still has trailing spaces. Enter =TRIM(H1) in I1 and copy down. This will eliminate the trailing spaces and any double spaces in the data in column H. Copy and Paste Values to get rid of the formulae. Delete the original data and column H of the new data.

This should clean up your data ready to be formatted in the manner that you want and allow you to enter the delimiters (whatever they are) that you are having trouble with.