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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
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.
 

brent.fraser

New Member
Joined
Aug 27, 2009
Messages
41
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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
You're welcome. Thanks for the feedback.

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

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

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