Replacing VBcr with "," in a string

[Guest]

I have imported into a table an XML file which details addresses. In the
table in shows the address as follows: 1635 W National Ave *Milwaukee, WI
53204-1130

The * is actually a little square box which I presume is the VBcr symbol.

I want to replace this with ",". How do I do it.

I have tried
Old = Replace(Old, Vbcr, ",", , , vbTextCompare)
but this does not work.

Thanks in advance.

 

[Guest]

It may be you are not trying to replace the correct value with Replace
function.
vbCr is the same as Chr(13) and vbLf is the same as Chr(10). What you may
be looking for is the combination of the two Chr(13) & Chr(10) which is the
same as vbCrLf.

You can test the varialbe or control the string is in to see what they
really are. Let's say you know the position of the first character is in
postion 6, you could do this in the immediate window:

?Asc(Mid(SomeVariable, 6, 1))

 

[Guest]

Klatuu,

Thanks very much it is Chr(9). Have no idea how to find out what this
actually is but have ammended code as follows and it works.

Where do I go to find out what this character is?

Old = Replace(Old, Chr(9), ",", , , vbTextCompare)

Thank you very much for your help. Much appreciated.

 

[Guest]

Klatuu,

Sorry found out chr(9) is a tab.

Thanks again. Very good of you to help.

 

[Guest]

chr(9) is the Tab character. It is the same as vbTab, so
Old = Replace(Old, vbTab, ",")
is sufficient.