Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I use macro to find and replace unicode characters
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I use macro to find and replace unicode characters
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? -- Rick (MVP - Excel) "CliffG" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I use macro to find and replace unicode characters
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" wrote: 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? -- Rick (MVP - Excel) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I use macro to find and replace unicode characters
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)? -- Rick (MVP - Excel) "CliffG" wrote in message ... 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" wrote: 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? -- Rick (MVP - Excel) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I use macro to find and replace unicode characters
Absolutely :)
U+255B .75 U+255C .50 U+255D .25 "Rick Rothstein" wrote: 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)? -- Rick (MVP - Excel) "CliffG" wrote in message ... 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" wrote: 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? -- Rick (MVP - Excel) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I use macro to find and replace unicode characters
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) "CliffG" wrote in message ... Absolutely :) U+255B .75 U+255C .50 U+255D .25 "Rick Rothstein" wrote: 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)? -- Rick (MVP - Excel) "CliffG" wrote in message ... 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" wrote: 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? -- Rick (MVP - Excel) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I use macro to find and replace unicode characters
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 "Rick Rothstein" wrote: 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) "CliffG" wrote in message ... Absolutely :) U+255B .75 U+255C .50 U+255D .25 "Rick Rothstein" wrote: 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)? -- Rick (MVP - Excel) "CliffG" wrote in message ... 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" wrote: 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? -- Rick (MVP - Excel) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I use macro to find and replace unicode characters
You don't integrate it with what the macro recorder gave you... forget what
the macro recorder gave you... delete it and copy/paste my macro in its place. Oh, and change the sheet name I used (Sheet6) in the With statement to the name of the worksheet where your data at... then, just run the macro and it should replace the three symbols you showed me (, and ) with the numbers .25, .5 and .75 wherever they appear on the worksheet. -- Rick (MVP - Excel) "CliffG" wrote in message ... 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 "Rick Rothstein" wrote: 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) "CliffG" wrote in message ... Absolutely :) U+255B .75 U+255C .50 U+255D .25 "Rick Rothstein" wrote: 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)? -- Rick (MVP - Excel) "CliffG" wrote in message ... 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" wrote: 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? -- Rick (MVP - Excel) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I use macro to find and replace unicode characters
Hey Rick,
Well, you were right..it works. Yippee Like I said, I'm not a coder, so I don't know how (wish I did, I do like to follow the logic). Just two issues remain, the first I think you can fix, hopefully the second. The values .75 and .25 are switched when replaced in the spreadsheet. The .50 is replaced correctly. The second issue is that the replaced values include a zero before the decimal point, i.e., the replacement values are 0.25, 0.50, 0.75. Many of the cells already contain a whole number, so for example, 8 becomes 80.50 instead of the desired 8.50. Thanks for your effort, I really do appreciate it. "Rick Rothstein" wrote: You don't integrate it with what the macro recorder gave you... forget what the macro recorder gave you... delete it and copy/paste my macro in its place. Oh, and change the sheet name I used (Sheet6) in the With statement to the name of the worksheet where your data at... then, just run the macro and it should replace the three symbols you showed me (, and ) with the numbers .25, .5 and .75 wherever they appear on the worksheet. -- Rick (MVP - Excel) |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I use macro to find and replace unicode characters
Okay, try this variation...
Sub ConvertSymbolsToValues() Dim X As Long Dim C As Range With Worksheets("Sheet1").UsedRange For X = 1 To 3 Set C = .Find(What:=ChrW(9566 - X), LookAt:=xlPart) If Not C Is Nothing Then Do C.Value = Replace(C.Value, ChrW(9566 - X), Format(X / 4, ".##")) Set C = .FindNext(C) Loop While Not C Is Nothing End If Next End With End Sub -- Rick (MVP - Excel) "CliffG" wrote in message ... Hey Rick, Well, you were right..it works. Yippee Like I said, I'm not a coder, so I don't know how (wish I did, I do like to follow the logic). Just two issues remain, the first I think you can fix, hopefully the second. The values .75 and .25 are switched when replaced in the spreadsheet. The .50 is replaced correctly. The second issue is that the replaced values include a zero before the decimal point, i.e., the replacement values are 0.25, 0.50, 0.75. Many of the cells already contain a whole number, so for example, 8 becomes 80.50 instead of the desired 8.50. Thanks for your effort, I really do appreciate it. "Rick Rothstein" wrote: You don't integrate it with what the macro recorder gave you... forget what the macro recorder gave you... delete it and copy/paste my macro in its place. Oh, and change the sheet name I used (Sheet6) in the With statement to the name of the worksheet where your data at... then, just run the macro and it should replace the three symbols you showed me (, and ) with the numbers .25, .5 and .75 wherever they appear on the worksheet. -- Rick (MVP - Excel) |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I use macro to find and replace unicode characters
Beautiful, perfect...thank you so much. :) If you have the time and are
inclined, could you give me a brief explaination of what is going on in this script? I have done some javascript and php (just enough for my own purposes), so I can usually at least follow the logic in a routine. This one has me scratching my head. I don't see anything familar like variables being manipulated. Where do you get ChrW(9566 - X) for example. My curiosity is overwhelming me. Thanks again, Cliff "Rick Rothstein" wrote: Okay, try this variation... Sub ConvertSymbolsToValues() Dim X As Long Dim C As Range With Worksheets("Sheet1").UsedRange For X = 1 To 3 Set C = .Find(What:=ChrW(9566 - X), LookAt:=xlPart) If Not C Is Nothing Then Do C.Value = Replace(C.Value, ChrW(9566 - X), Format(X / 4, ".##")) Set C = .FindNext(C) Loop While Not C Is Nothing End If Next End With End Sub -- Rick (MVP - Excel) "CliffG" wrote in message ... Hey Rick, Well, you were right..it works. Yippee Like I said, I'm not a coder, so I don't know how (wish I did, I do like to follow the logic). Just two issues remain, the first I think you can fix, hopefully the second. The values .75 and .25 are switched when replaced in the spreadsheet. The .50 is replaced correctly. The second issue is that the replaced values include a zero before the decimal point, i.e., the replacement values are 0.25, 0.50, 0.75. Many of the cells already contain a whole number, so for example, 8 becomes 80.50 instead of the desired 8.50. Thanks for your effort, I really do appreciate it. "Rick Rothstein" wrote: You don't integrate it with what the macro recorder gave you... forget what the macro recorder gave you... delete it and copy/paste my macro in its place. Oh, and change the sheet name I used (Sheet6) in the With statement to the name of the worksheet where your data at... then, just run the macro and it should replace the three symbols you showed me (, and ) with the numbers .25, .5 and .75 wherever they appear on the worksheet. -- Rick (MVP - Excel) |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I use macro to find and replace unicode characters
See if this commented version of the code helps you out...
Sub ConvertSymbolsToValues() ' It's a good idea to always declare your variables Dim X As Long Dim C As Range ' The With statement is establishing the object that the Find and ' FindNext methods will operate on (the UsedRange of Sheet1). We ' do this to limit the search to an area where values are known to be With Worksheets("Sheet1").UsedRange ' You have three symbols to replace, and their Unicode values are ' in sequential order, so we need to loop three times incrementing ' the loop by one each time For X = 1 To 3 ' The Hex values for your symbols are 255B, 255C, 255D... the ' equivalent converted Decimal values for these Hex values ' are 9563, 9564 and 9565 respectively. In order to produce the ' fractional values you want in the order you want them, we need ' to traverse the Decimal values in reverse order (9565 is 0.25, ' 9564 is 0.5 and 9563 is 0.75). We do that by subtracting 1, 2 ' and 3 from 9566 to produce the value 9565, 9564 and 9563 in ' that order. So, each time through the loop, we will look for ' the Unicode character with code value of 9565, 9564 and 9563 ' in that order Set C = .Find(What:=ChrW(9566 - X), LookAt:=xlPart) ' Test if the currently being looked for symbol was found ' anywhere within the UsedRange on Sheet1 If Not C Is Nothing Then ' If so, let's look for all of that current symbol within the ' UsedRange on Sheet1 Do ' We know from the If..Then test above that C is referencing ' one of the symbols we were searching for, so let's replace ' each occurrence of that symbol with its equivalent fraction ' (which we calculate by dividing the current loop index ' (which is 1, 2 or 3) by 4 to produce .25, .5, or .75 ' depending on the value of the loop index C.Value = Replace(C.Value, ChrW(9566 - X), Format(X / 4, ".##")) ' Look for the current symbol elsewhere within the UsedRange Set C = .FindNext(C) ' If C is not nothing, then we found another symbol somewhere, ' so loop to process it. If C is nothing, then there are no more ' instances of that current symbol, so drop out of the Do loop ' back into the For..Next loop to look for the next symbol Loop While Not C Is Nothing End If Next End With End Sub -- Rick (MVP - Excel) "CliffG" wrote in message ... Beautiful, perfect...thank you so much. :) If you have the time and are inclined, could you give me a brief explaination of what is going on in this script? I have done some javascript and php (just enough for my own purposes), so I can usually at least follow the logic in a routine. This one has me scratching my head. I don't see anything familar like variables being manipulated. Where do you get ChrW(9566 - X) for example. My curiosity is overwhelming me. Thanks again, Cliff "Rick Rothstein" wrote: Okay, try this variation... Sub ConvertSymbolsToValues() Dim X As Long Dim C As Range With Worksheets("Sheet1").UsedRange For X = 1 To 3 Set C = .Find(What:=ChrW(9566 - X), LookAt:=xlPart) If Not C Is Nothing Then Do C.Value = Replace(C.Value, ChrW(9566 - X), Format(X / 4, ".##")) Set C = .FindNext(C) Loop While Not C Is Nothing End If Next End With End Sub -- Rick (MVP - Excel) "CliffG" wrote in message ... Hey Rick, Well, you were right..it works. Yippee Like I said, I'm not a coder, so I don't know how (wish I did, I do like to follow the logic). Just two issues remain, the first I think you can fix, hopefully the second. The values .75 and .25 are switched when replaced in the spreadsheet. The .50 is replaced correctly. The second issue is that the replaced values include a zero before the decimal point, i.e., the replacement values are 0.25, 0.50, 0.75. Many of the cells already contain a whole number, so for example, 8 becomes 80.50 instead of the desired 8.50. Thanks for your effort, I really do appreciate it. "Rick Rothstein" wrote: You don't integrate it with what the macro recorder gave you... forget what the macro recorder gave you... delete it and copy/paste my macro in its place. Oh, and change the sheet name I used (Sheet6) in the With statement to the name of the worksheet where your data at... then, just run the macro and it should replace the three symbols you showed me (, and ) with the numbers .25, .5 and .75 wherever they appear on the worksheet. -- Rick (MVP - Excel) |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I use macro to find and replace unicode characters
That's super, Rick. I "get" it now. :) They're not paying you enough.
Cliff "Rick Rothstein" wrote: See if this commented version of the code helps you out... Sub ConvertSymbolsToValues() ' It's a good idea to always declare your variables Dim X As Long Dim C As Range ' The With statement is establishing the object that the Find and ' FindNext methods will operate on (the UsedRange of Sheet1). We ' do this to limit the search to an area where values are known to be With Worksheets("Sheet1").UsedRange ' You have three symbols to replace, and their Unicode values are ' in sequential order, so we need to loop three times incrementing ' the loop by one each time For X = 1 To 3 ' The Hex values for your symbols are 255B, 255C, 255D... the ' equivalent converted Decimal values for these Hex values ' are 9563, 9564 and 9565 respectively. In order to produce the ' fractional values you want in the order you want them, we need ' to traverse the Decimal values in reverse order (9565 is 0.25, ' 9564 is 0.5 and 9563 is 0.75). We do that by subtracting 1, 2 ' and 3 from 9566 to produce the value 9565, 9564 and 9563 in ' that order. So, each time through the loop, we will look for ' the Unicode character with code value of 9565, 9564 and 9563 ' in that order Set C = .Find(What:=ChrW(9566 - X), LookAt:=xlPart) ' Test if the currently being looked for symbol was found ' anywhere within the UsedRange on Sheet1 If Not C Is Nothing Then ' If so, let's look for all of that current symbol within the ' UsedRange on Sheet1 Do ' We know from the If..Then test above that C is referencing ' one of the symbols we were searching for, so let's replace ' each occurrence of that symbol with its equivalent fraction ' (which we calculate by dividing the current loop index ' (which is 1, 2 or 3) by 4 to produce .25, .5, or .75 ' depending on the value of the loop index C.Value = Replace(C.Value, ChrW(9566 - X), Format(X / 4, ".##")) ' Look for the current symbol elsewhere within the UsedRange Set C = .FindNext(C) ' If C is not nothing, then we found another symbol somewhere, ' so loop to process it. If C is nothing, then there are no more ' instances of that current symbol, so drop out of the Do loop ' back into the For..Next loop to look for the next symbol Loop While Not C Is Nothing End If Next End With End Sub -- Rick (MVP - Excel) "CliffG" wrote in message ... Beautiful, perfect...thank you so much. :) If you have the time and are inclined, could you give me a brief explaination of what is going on in this script? I have done some javascript and php (just enough for my own purposes), so I can usually at least follow the logic in a routine. This one has me scratching my head. I don't see anything familar like variables being manipulated. Where do you get ChrW(9566 - X) for example. My curiosity is overwhelming me. Thanks again, Cliff "Rick Rothstein" wrote: Okay, try this variation... Sub ConvertSymbolsToValues() Dim X As Long Dim C As Range With Worksheets("Sheet1").UsedRange For X = 1 To 3 Set C = .Find(What:=ChrW(9566 - X), LookAt:=xlPart) If Not C Is Nothing Then Do C.Value = Replace(C.Value, ChrW(9566 - X), Format(X / 4, ".##")) Set C = .FindNext(C) Loop While Not C Is Nothing End If Next End With End Sub -- Rick (MVP - Excel) "CliffG" wrote in message ... Hey Rick, Well, you were right..it works. Yippee Like I said, I'm not a coder, so I don't know how (wish I did, I do like to follow the logic). Just two issues remain, the first I think you can fix, hopefully the second. The values .75 and .25 are switched when replaced in the spreadsheet. The .50 is replaced correctly. The second issue is that the replaced values include a zero before the decimal point, i.e., the replacement values are 0.25, 0.50, 0.75. Many of the cells already contain a whole number, so for example, 8 becomes 80.50 instead of the desired 8.50. Thanks for your effort, I really do appreciate it. "Rick Rothstein" wrote: You don't integrate it with what the macro recorder gave you... forget what the macro recorder gave you... delete it and copy/paste my macro in its place. Oh, and change the sheet name I used (Sheet6) in the With statement to the name of the worksheet where your data at... then, just run the macro and it should replace the three symbols you showed me (, and ) with the numbers .25, .5 and .75 wherever they appear on the worksheet. -- Rick (MVP - Excel) |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I use macro to find and replace unicode characters
Whose "they"? I'm retired from my previous job as a Civil Engineer for
several years now and my efforts here in the newsgroups are strictly voluntary. While I have been honored with an MVP designation from Microsoft, you (as well as others here) should know that MVPs are not employees of Microsoft... we do not get paid for our "work" here on the newsgroups. -- Rick (MVP - Excel) "CliffG" wrote in message ... That's super, Rick. I "get" it now. :) They're not paying you enough. Cliff "Rick Rothstein" wrote: See if this commented version of the code helps you out... Sub ConvertSymbolsToValues() ' It's a good idea to always declare your variables Dim X As Long Dim C As Range ' The With statement is establishing the object that the Find and ' FindNext methods will operate on (the UsedRange of Sheet1). We ' do this to limit the search to an area where values are known to be With Worksheets("Sheet1").UsedRange ' You have three symbols to replace, and their Unicode values are ' in sequential order, so we need to loop three times incrementing ' the loop by one each time For X = 1 To 3 ' The Hex values for your symbols are 255B, 255C, 255D... the ' equivalent converted Decimal values for these Hex values ' are 9563, 9564 and 9565 respectively. In order to produce the ' fractional values you want in the order you want them, we need ' to traverse the Decimal values in reverse order (9565 is 0.25, ' 9564 is 0.5 and 9563 is 0.75). We do that by subtracting 1, 2 ' and 3 from 9566 to produce the value 9565, 9564 and 9563 in ' that order. So, each time through the loop, we will look for ' the Unicode character with code value of 9565, 9564 and 9563 ' in that order Set C = .Find(What:=ChrW(9566 - X), LookAt:=xlPart) ' Test if the currently being looked for symbol was found ' anywhere within the UsedRange on Sheet1 If Not C Is Nothing Then ' If so, let's look for all of that current symbol within the ' UsedRange on Sheet1 Do ' We know from the If..Then test above that C is referencing ' one of the symbols we were searching for, so let's replace ' each occurrence of that symbol with its equivalent fraction ' (which we calculate by dividing the current loop index ' (which is 1, 2 or 3) by 4 to produce .25, .5, or .75 ' depending on the value of the loop index C.Value = Replace(C.Value, ChrW(9566 - X), Format(X / 4, ".##")) ' Look for the current symbol elsewhere within the UsedRange Set C = .FindNext(C) ' If C is not nothing, then we found another symbol somewhere, ' so loop to process it. If C is nothing, then there are no more ' instances of that current symbol, so drop out of the Do loop ' back into the For..Next loop to look for the next symbol Loop While Not C Is Nothing End If Next End With End Sub -- Rick (MVP - Excel) "CliffG" wrote in message ... Beautiful, perfect...thank you so much. :) If you have the time and are inclined, could you give me a brief explaination of what is going on in this script? I have done some javascript and php (just enough for my own purposes), so I can usually at least follow the logic in a routine. This one has me scratching my head. I don't see anything familar like variables being manipulated. Where do you get ChrW(9566 - X) for example. My curiosity is overwhelming me. Thanks again, Cliff "Rick Rothstein" wrote: Okay, try this variation... Sub ConvertSymbolsToValues() Dim X As Long Dim C As Range With Worksheets("Sheet1").UsedRange For X = 1 To 3 Set C = .Find(What:=ChrW(9566 - X), LookAt:=xlPart) If Not C Is Nothing Then Do C.Value = Replace(C.Value, ChrW(9566 - X), Format(X / 4, ".##")) Set C = .FindNext(C) Loop While Not C Is Nothing End If Next End With End Sub -- Rick (MVP - Excel) "CliffG" wrote in message ... Hey Rick, Well, you were right..it works. Yippee Like I said, I'm not a coder, so I don't know how (wish I did, I do like to follow the logic). Just two issues remain, the first I think you can fix, hopefully the second. The values .75 and .25 are switched when replaced in the spreadsheet. The .50 is replaced correctly. The second issue is that the replaced values include a zero before the decimal point, i.e., the replacement values are 0.25, 0.50, 0.75. Many of the cells already contain a whole number, so for example, 8 becomes 80.50 instead of the desired 8.50. Thanks for your effort, I really do appreciate it. "Rick Rothstein" wrote: You don't integrate it with what the macro recorder gave you... forget what the macro recorder gave you... delete it and copy/paste my macro in its place. Oh, and change the sheet name I used (Sheet6) in the With statement to the name of the worksheet where your data at... then, just run the macro and it should replace the three symbols you showed me (, and ) with the numbers .25, .5 and .75 wherever they appear on the worksheet. -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace Special characters with numbers | Excel Discussion (Misc queries) | |||
FIND and REPLACE characters needed | New Users to Excel | |||
FIND and REPLACE characters needed | Excel Worksheet Functions | |||
How do I find replace special characters? | Excel Discussion (Misc queries) | |||
Find and replace unusual characters ... | Excel Discussion (Misc queries) |