Removing extended dash
 

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

Removing extended dash
 

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

Removing extended dash
 

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

Removing extended dash
 

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

Removing extended dash
 

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

Removing extended dash
 

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

Removing extended dash
 

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

Removing extended dash
 

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

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

Removing extended dash
 

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!


---
Regards,
Norman



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

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