Funny Character ????

L

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 ***
 
B

Bernie Deitrick

Les,

With that string in cell A2, try the formula

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

HTH,
Bernie
MS Excel MVP
 
T

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.
 
L

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 ***
 
L

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 ***
 
G

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
 
L

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 ***
 
T

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.
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top