Funny Character ????

[Les Stout]

Hi, i download from a main frame and get the e-mail address below but it
has a funny character that excel sees as a "?" but when i code it, it is
not found ?

MARIUS.EKSTEEN¹BMW.CO.ZA

Could anybody help please !!!



Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Bernie Deitrick]

Les,

With that string in cell A2, try the formula

=SUBSTITUTE(A2,CHAR(185),"@")

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

assume this is in cell A1 and the question mark is displayed at the end of
MARIUS - it is the 7th character in the string (as an example since no
question mark is present in your post).

=code(Mid(A1,7,1))

would tell you what the character code is for unknown character. You could
then consult the character map applet if you have it installed.

 

[Les Stout]

Thanks for the replies, i see the character has changed from what it was
to a superscript 1 and then to a 9 ??

I actually need to do this with code as we are talking about a variable
amount of rows, from 5000 upwards... Thanks

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Les Stout]

Thanks Tom, i have established that it is Char (63), could you please
help me put this into some code, the e-mail addresses are all in column
E...

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Gary Keramidas]

didn't follow the entire thread, so hopefully it's relevant

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "E").End(xlUp).Row
For i = 1 To lastrow
ws.Range("E" & i).Value = Replace(ws.Range("E" & i).Value, Chr(63), "@", 1)
Next
End Sub

 

[Les Stout]

Thanks for that Gary, i used Toms method to find out what the char
number was, but 63 is a "c" and it does not look like one either.... It
does not replace the "c's" in the string either ?? can you help ??

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Tom Ogilvy]

Sub FixCharacter()

Columns(5).Replace What:=Chr(63), _
Replacement:="@", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

End Sub

Replace the "@" with whatever you want the replacement to be.

 

[Les Stout]

Thanks Tom, please see my last thread...

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Les Stout]

Hi Tom, i still have your e-mail address and took the liberty of sending
you an example, hope you dont mind...

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Tom Ogilvy]

Its a Unicode character. This appeared to work:

Sub ProcessCharacters()
Dim rng As Range, s As String
Dim cell As Range, b() As Byte
Dim i As Long
Range("E4").Select
Set rng = Range(ActiveCell, _
Cells(Rows.Count, ActiveCell.Column).End(xlUp))
For Each cell In rng
Application.StatusBar = "Row: " & cell.Row
s = cell.Value
b = s
For i = LBound(b) To UBound(b) Step 2
If b(i) = 121 Then
If b(i + 1) = 255 Then
b(i) = 64
b(i + 1) = 0
Exit For
End If
End If
Next
s = b
cell.Value = s
Next
Application.StatusBar = False
End Sub

 

[Les Stout]

Thanks very much Tom, works 100%. This site would be lost without you i
am sure... :0)

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***