how do I use macro to find and replace unicode characters

[CliffG]

I have database files that I import into Excel 2003. In certain columns
there are unicode characters, e.g. ╜ . I use the find and replace function
to replace these charactrers with numbers. I can do this fine by hand. When
I record a macro to do the same, it doesn't work. The macro replaces the ╜
characters with the plus sign "+" which breaks it. I've tried editing the
macro by placing the ╜ characters in the formula, but that doesn't work
either. The editor won't allow it, replacing the ╜ with a question mark "?".
Does anyone know how I can get this to work. I do this almost daily I would
hate to do it manually every time.

 

[Rick Rothstein]

Can you give us a hint of what you want to replace them with? You say
"numbers"... what numbers and how are the numbers related to the symbols
(for example, are they their ASCII code)? Is replacing them with numbers all
you want to do to them, or is that in intermediate step on the way to some
other functionality? Also, are these symbols confined to specific columns
(if so, which ones) or can they be anywhere within your data?

 

[CliffG]

Thanks in advance for your help.

In the original database the symbols represented fractions, i.e., ¼, ½, or
¾. I replace these with their decimal equivilents .25, .50, .75. They are
contained in two columns only, in this case G and I. I basically want save
it in that format. The saved spreadsheet is ultimately imported into another
database.

As I mentioned, I can do it fine by pasting the symbols from character map
into "find" and typing in their replacement values. I can save the
spreadsheet, import it later, everything is right. When I try to record the
steps in the macro, it just doesn't record it correctly. When I run the
macro it doesn't find anything to replace because it's looking for "+"
instead of the symbols.

Thanks again.

 

[Rick Rothstein]

I'm pretty sure we can write a macro for you ... can you post the conversion
chart (Unicode character numbers against the fractional values you want it
to be)?

 

[CliffG]

Absolutely

â•› U+255B .75
╜ U+255C .50
â• U+255D .25

 

[Rick Rothstein]

Give this macro a try...

Sub ConvertSymbolsToValues()
Dim X As Long
Dim C As Range
With Worksheets("Sheet6").UsedRange
For X = 1 To 3
Set C = .Find(What:=ChrW(9562 + X), LookAt:=xlPart)
If Not C Is Nothing Then
Do
C.Value = Replace(C.Value, ChrW(9562 + X), X / 4)
Set C = .FindNext(C)
Loop While Not C Is Nothing
End If
Next
End With
End Sub

--
Rick (MVP - Excel)

Absolutely

â•› U+255B .75
╜ U+255C .50
â• U+255D .25

 

[CliffG]

Hey Rick,

I certainly would give it a try, if I had a clue. I can generally follow a
script, but I'm not a programer. I don't know how to integrate your script
with what I'm trying to do. Here the macro that Excel produced when I
recorded it:

Sub Macro8()
Selection.Replace What:="+", Replacement:=".75", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="+", Replacement:=".5", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="+", Replacement:=".25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = "0.00"
End Sub

If you could please step me through how I need to use your script, I would
greatly appreciate it.

Thanks,

Cliff