Deal with text characters that are greater than chr(127)

[John Keith]

I am dealing with a spreadsheet that is downloaded from another
application. The parent application allows html text and includes some
bullits. When this text is downlaoded to an excel spreadhseet the
bullits are converted to the followng text string:

•

The ¢ is chr(128) and the € is chr(162) (I may have these reversed?)
but I have not been able to determine the code for the â.

But indepedent of not knowing the code for the one character I'm
having trouble dealing with these characters. Using a manual search
for just one character nothing is found.

I'd like to replace this string of characters (•) with a "-" and
maybe a CR/LF at the start so that the resulting text looks similar to
the html.

How do I deal with these unusual characters?

Thanks for the suggestions!


John Keith
(e-mail address removed)

 

[Gary Keramidas]

not sure what characters you need, but if all you need are alphanumeric, 122
should be the highest number you need. if it's > 123, then delete it.
or you can test for the following values:

48 - 57 are 0-9
65 - 90 are A-Z
97-122 are a-z

or look at the character map in windows to find your character.

 

[Mishell]

Sub ASCII_Value_Of_a_Character()

MsgBox Asc(Mid("â?¢", 1, 1))

End Sub


Sub Replace_By_Something()

a = Cells(1, 1).Formula

Replace_What = "â?¢"
Replace_By = vbLf '"-"

b = InStr(a, Replace_What)
While b > 0
a = Mid(a, 1, b - 1) & Replace_By & Mid(a, b + Len(Replace_What))
b = InStr(a, Replace_What)
Wend

Cells(1, 1).Formula = a

End Sub


Docm

 

[joel]

I usually crate a sime macro for debugging these
probelms


InputSr = "abcdefghij"
rowcount = 1
for i = 1 to len(InputStr)
cells(RowCount,i) = asc(mid(InputStr,i,1))
next i

to replaced the character one you find the results use this

Replace_What = chr(128) & chr(162) & chr(x) 'x is last character
Replace_By = vbLf '"-"

b = InStr(a, Replace_What)
While b > 0
a = Mid(a, 1, b - 1) & Replace_By & Mid(a, b + Len(Replace_What))
b = InStr(a, Replace_What)
Wend

Cells(1, 1).Formula = a

 

[John Keith]

or look at the character map in windows to find your character.

Gary,

Thank you for the suggestion to look at the character map. I now have
some basic info on each character (I had to select several diffeerent
fonts before I found each character):

keystroke to enter:

ALT + 0128 €
ALT + 0162 ¢
ALT + 0226 â

This insight along with the other suggestions may solve the issue.

This exercise will be another learning experience! I've not had to
deal with what I would call non-standard ASCII before.


John Keith
(e-mail address removed)

 

[John Keith]

I usually crate a sime macro for debugging these
probelms

Joel,

I had started with some simple cut and paste into the Find and Replace
in Excel but was not successful with that. I also used a macro to
simply create all ASCII characters and even that didn't get me to any
useful results.

Later today I'll play with your suggestion.

Thanks


John Keith
(e-mail address removed)

 

[John Keith]

Mishell,

Thank oyu for your suggestion. It appears to be somewhat similar to
the method Joel suggested and I will give it a try later today as
well.



John Keith
(e-mail address removed)

 

[John Keith]

Thanks for the pointers, the problem has been solved!

The simple code line below replaces all occurences of the characters I
wanted to find:

Cells.Replace What:="•", Replacement:=Chr(10) & "-"

The characters are entered inside the quotes by using the key
sequences noted below:

keystroke to enter:

ALT + 0226 â
ALT + 0128 €
ALT + 0162 ¢

John Keith
(e-mail address removed)

 

[Gary Keramidas]

glad you got it worked out.