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

Hi Mark,

Try:

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

---
Regards,
Norman



"Mark" wrote in message
...
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

Hi Mark,

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

Replacement:="" to Replacement:=0,

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Mark,

Try:

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

---
Regards,
Norman



"Mark" wrote in message
...
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

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

Thanks

Mark

"Norman Jones" wrote:

Hi Mark,

Try:

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

---
Regards,
Norman



"Mark" wrote in message
...
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

Hi Mark,

With just the character selected, typing

?asc(selection)

in the intermediate window will give you the Chr number.

---
Regards,
Norman



"Mark" wrote in message
...
Can you give me some other Chr suggestions please, it isn't that one :-(

Thanks

Mark

"Norman Jones" wrote:

Hi Mark,

Try:

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

---
Regards,
Norman



"Mark" wrote in message
...
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

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" wrote:

Hi Mark,

With just the character selected, typing

?asc(selection)

in the intermediate window will give you the Chr number.

---
Regards,
Norman



"Mark" wrote in message
...
Can you give me some other Chr suggestions please, it isn't that one :-(

Thanks

Mark

"Norman Jones" wrote:

Hi Mark,

Try:

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

---
Regards,
Norman



"Mark" wrote in message
...
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

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.

---
Regards,
Norman


"Mark" wrote in message
...
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" wrote:

Hi Mark,

With just the character selected, typing

?asc(selection)

in the intermediate window will give you the Chr number.

---
Regards,
Norman



"Mark" wrote in message
...
Can you give me some other Chr suggestions please, it isn't that one
:-(

Thanks

Mark

"Norman Jones" wrote:

Hi Mark,

Try:

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

---
Regards,
Norman



"Mark" wrote in message
...
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