Removing extended dash

[Guest]

I have imported some data from a table on our local intranet but there is
character in some of the cells which is an extended version of the normal
dash like this one ' − '
Wheras this is normal keyboard dash ' - '

I have tried doing a find/replace to make it a zero but it is not found when
this character is placed in VBA it looks just like a normal dash.

Can anyone assist with a solution, please?

Mark

 

[Norman Jones]

Hi Mark,

Try:

Sub RemoveChar151()
Selection.Replace What:=Chr(151), _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

 

[Norman Jones]

Hi Mark,

If you want the double-hyphen character replaced with zero - I missed this -
change:

Replacement:="" to Replacement:=0,

 

[Guest]

Can you give me some other Chr suggestions please, it isn't that one :-(

Thanks

Mark

 

[Norman Jones]

Hi Mark,

With just the character selected, typing

?asc(selection)

in the intermediate window will give you the Chr number.

 

[Guest]

I'm back :-(

using your last states it is 45

I have used your code on a couple of other characters in another cell and it
works fine but it will not change 45, I've even tired it in another cell!

Mark

 

[Norman Jones]

Hi Mark,

Chr(45) is a standard dash character.

After changing:

What:=Chr(151) to What:=Chr(45)

my macro successfully removed various manually inserted Chr(45) characters
on my test sheet. Similarly, entering a dah in the first box of the Excel
Edit/Replace dialog should work!

If you are, however, still experiencing difficulties, copy the mysterious
character into (say) cell A1 and try this version which should replace your
character whatever it is:

Sub RemoveFunnyChar()
Selection.Replace What:=Range("A1").Value, _
Replacement:=0, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Amend A1 as required.

 

[Guest]

Norman,

Yes I checked and saw that chr(45) is the normal dash, it's obviously
something to do with pasting out of IE.

I have just used your last and that works, but then it would because no
matter what it's there it's going to get changed.

Anyway, thanks very much for your time, looks like i'm going to have to do
it manually.

Mark

 

[Norman Jones]

Hi Mark,

I suspect that we are at cross-purposes!

If you copy the character into cell A1, select the relevant range and run
the last version, all occurrences should be replaced with zeros in one go!