Replace all symbols with text

brent.fraser

New Member
Joined
Aug 27, 2009
Messages
41
Hey everyone, I am importing content control information in a word file into excel by using a macro in excel. The information is coming across nicely but when the information about a checkbox comes across, in excel, that's what you see, a checkbox (☐ or ☒). Is there any way (formula or vba) to replace all the checkboxes with a yes or a no?

I wrote a formula that works:
Code:
=IF(G8="☒","YeS","nO")
but this will add this information to a new column and I would like to replace the information in the existing column (the form has about 200 checkboxes) and I don't want to add a bunch of new columns.

I also toyed with VBA:
Code:
Range("G1:G100").Replace "&", "No"
but when I try to paste the ☐ in the vba code, I get a "?" so I am assuming it doesn't recognize the ☐ as a valid character.

Any thoughts?

Thanks,
Brent
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi Brent

You can use the Range.Replace method, you just have to know which character you are replacing.

This is what I did.

1 - pasted the character ☒from your post into the cell A1
2 - executed

Code:
MsgBox AscW(Range("A1").Value)

This gave me the code of the symbol: 9746

3 - to replace this character, for ex. in columns B:F, I executed:

Code:
Columns("B:F").Replace what:=ChrW(9746), Replacement:="Yes", LookAt:=xlWhole


Test and adapt to your case.
 
Upvote 0
Thank you so much! This worked out wonderfully! Exactly what I was looking for. Thanks PGC!!!

I am not sure if you are a moderator but when I was posting this topic, my computer kept on freezing and every time I refreshed, it posted the topic so there are three of the same topic out there.... I can't delete them on my own however. I am not sure if you can.

Anyway, thanks for your help. It worked out very well.

Brent
 
Upvote 0
You're welcome. Thanks for the feedback.

Remark: I can't delete the threads. You should use the report button to report the situation.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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
Back
Top