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

Les,

With that string in cell A2, try the formula

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

HTH,
Bernie
MS Excel MVP


"Les Stout" wrote in message ...
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 ***

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.

--
Regards,
Tom Ogilvy




"Les Stout" wrote:

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.EKSTEEN9BMW.CO.ZA

Could anybody help please !!!



Best regards,

Les Stout

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

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

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

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

--


Gary


"Les Stout" wrote in message
...
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 ***

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

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.

--
Regards,
Tom Ogilvy

"Les Stout" wrote:

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

Thanks Tom, please see my last thread...

Best regards,

Les Stout

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

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