Unwanted character, can not search and replace!

[HAL-9000]

Hi

I have a sheet that I often have to edit and import into SQL database.
I have no control over the format of the sheet given to me and it seems that something has changed causing me the following problem.
When I try to import the Data it’s showing ten times as many items than are actually there.

The problem I think is in a text column. I’ve tested this by using an old column from a previous sheet and pasting this into the new sheet and I can import it ok.

On searching this site I found this post http://www.mrexcel.com/forum/showthread.php?t=127146

I’ve tried a few pointers from this but no luck.
What I seem to have is a rectangular box character but I’m not sure what it is.
I can copy and past it into Notepad as it is, but pasted into Word it seems to be a carriage Return.
So whether it’s an actual charter or not I’m not sure.

Are there any tools out there so I can hover over characters in Excel to see what it is! So at least I can be sure what I’m trying to Search and Replace?

Any ideas most welcome

 

[Norie]

Where is this data coming from?

As far as I can see it's HTML code, and if that's the case then the character's code is probably 160.

Which should be dealt with here in John's code.

Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

How exactly are you running that code?

 

[HAL-9000]

Thanx RichardSchollar, just sent you an email!

Norie; The data is from my supplier who I'm still waiting to receive a reply from!
Actually some of the cells in that columns are just plain text, but yes most have some basic html!
As I said in my previous post I'm not great with Excel, I get by!

How exactly are you running that code?

I added the macro using the VB editor, then selected from Excel - Tools - Macros- Trimall etc! Is that correct?
Regards.

 

[Richard Schollar]

Hi

You appear to have multiple Line Feed characters in the cells - and running a test using CLEAN does seem to remove them (on my system anyway). After applying the formula in an empty adjacent column, you can copy&paste values to fix them.

I will mail you back the file to show you what I have done.

 

[Norie]

Did you actually select the range before running the code John posted?

 

[HAL-9000]

Hi

You appear to have multiple Line Feed characters in the cells - and running a test using CLEAN does seem to remove them (on my system anyway). After applying the formula in an empty adjacent column, you can copy&paste values to fix them.

I will mail you back the file to show you what I have done.

Thanx Richard
That looks to be working on a test I've just done on a sample file. I guess my lack of experience with Excel let me down 1st time around. I'll try that with the full file and see how I get on with importing it and post back the results.
Many thanx, this should get me out of trouble hopefully.

 

[HAL-9000]

Did you actually select the range before running the code John posted?

Hi Norie, you mean Datsmart, yes I'm sure about that. I selected the column but not the specific range if that's ok! Then ran the macro.
I'll do that again with just the specific range and see if that makes any difference! I still could have made an error as my knowledge of Excel is limited.

As long as the formatting remains in that column then Richard's tip should sort me out.
I'll test a bit later and post back to let you guys know the results, good or bad

 

[HAL-9000]

Thanx guys, I got my site updated and the text looks good, haven't found anything wrong so far
Thanx for all your help, very much appreciated.
I'll post back when I have time to test the macro again!

 

[konew1]

It looks like the unwanted characters are always the last (right hand) character of each data item. Depending on the volume of data you might try =LEFT(A1,LEN(A1)-1)

 

[jindon]

try

Sub test()
Dim r As Range
For Each r In Range("a1", Range("a" & rows.Count).End(xlUp))
    r = CleanAll(r.Value)
Next
End Sub
 
Function CleanAll(txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "[\f\n\r\t\v]"
    .Global = True
    CleanAll = .replace(txt,"")
End With
End Function

 

[HAL-9000]

Thanx I'll try these soon, the file format should not have changed, it never had these before, but thanx again for the help and workaround to sorting this.