Text Cell

[Guest]

I have a series of data imported into a spreadsheet. After Importing, there
is an apostrophie in the first character and two spaces at the end of the
text that the trim function will not remove.
Has anyone seen this error and if so do you know how to remove the
apostrophie and the end spaces?

 

[Tom Ogilvy]

ActiveCell.Value = Trim(Application.Substitute(ActiveCell.Text,chr(160),"
"))

worked for me.

 

[Tom Ogilvy]

As I said, it removed the apostrophe for me and use

ActiveCell.Value = ActiveCell.Text

or even
ActiveCell.Value = ActiveCell.Value

Is a common way to overcome this problem.

The apostrophe is treated as a formatting character and forces the value of
the cell to be text. Even if it stays, if you cell should be text, it
doesn't create a problem, but as I said, the method I provided does remove
it. I can only imagine you altered it in some way or the cell is other than
what you have described.

Re: Mid
it isn't looking at the letters as one. It is ignoring the apostrophy

Do an =Len(A1) on your cell and you will see the apostrophy isn't counted.

Regards,
Tom Ogilvy