Find replace macro signle byte with double byte

bizpioneer

New Member
Joined
Apr 28, 2014
Messages
31
So I am trying find replace macro to replace any % "single byte" to % double byte but looks like macro doesnt recogize double bytes

So here is my macro

Range("A1").Select
columns("K:N").Select
Selection.Replace What:="%", Replacement:="%", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("H1").Select
Range("A1").Select

When I run it just replace the single byte sign with a single byte sign, anyway I can fix that so it replaces it with the double byte?

Thanks,
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

First of all: all characters in excel vba are, by default, double byte characters.

I think what you want is to replace the character with code H0025 (the default Percent "%" sign, from the ascii table) by the character HFF05 (Unicode Fullwidth Percent Sign).

In vba you can specify a character given its code with ChrW()

I don't understand what the Select's are doing in your code. Using Select in vba is bad practice, it makes the code less efficient and more difficult to read.

If I understand correctly you want:


Code:
Columns("K:N").Replace What:="%", Replacement:=ChrW(&HFF05), LookAt:=xlPart, _
    SearchOrder:=xlByRows, SearchFormat:=False, ReplaceFormat:=False

Please try
 

bizpioneer

New Member
Joined
Apr 28, 2014
Messages
31
It worked! thanks a million. To answer your question the selects are there because I simply suck :D I will take them out in the future! Thanks again :)
 

bizpioneer

New Member
Joined
Apr 28, 2014
Messages
31

ADVERTISEMENT

Hey, it's me again. I would like to do the same thing, this time replacing the following signs " & ® with the double byte alternatives, any idea how to get the code for these? is there some kind of library that has all the codes for the double byte characters?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
To get the ascii code for a specific character, you can just type that character in a cell (say A1)
Then this formula in another cell will tell you it's code

=CODE(A1)
 

bizpioneer

New Member
Joined
Apr 28, 2014
Messages
31

ADVERTISEMENT

In this case % should show HFF05 but the formula is showing it as 63 any ideas?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Oh, differnet standard....

It's still valid, but in vba use
Replacement:=Chr(63)
instead of
Replacement:=ChrW(&HFF05)
 

bizpioneer

New Member
Joined
Apr 28, 2014
Messages
31
didn't work when I used =Chr(63) it replaced the sign with a question mark, while ChrW(&HFF05) worked perfectly :confused: here is the formula that I used

Range("A1").Select
Cells.Replace What:="%", Replacement:=Chr(63), LookAt:=xlPart, SearchOrder _
:=xlByRows, SearchFormat:=False, ReplaceFormat:=False
 

Watch MrExcel Video

Forum statistics

Threads
1,123,047
Messages
5,599,497
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top