Cleaning Data

[Faulks]

Hi All,

I am trying to automate the processing of data to a spreadsheet. I am copy and pasting the information into the spreadsheet and trying to write some vba to process it. The issue i have is that a lot of the data looks like it has trailing blanks (but there not!!!). I have used Trim and Clean but the data does not change, i have coded it correctly because i have used some sample data with trailing blanks and the code sorts it but it does nothing to the pasted data - any advice???

Thanks
Faulks

 

[VoG]

Try replacing Chr(160) with "".

 

[Faulks]

Thanks Peter, that almost worked like a dream. However it has still left 1 'space thingy' at the beginning of the data - any ideas what this could be, it's not a space as i've tried the Trim and Clean again and it's still there?

Thanks again
Faulks

 

[SteveO59L]

Try something like =code(left(a1,1)) to identify which character it is

 

[Faulks]

Thanks guys, you've solved it - much appreciated