Circling Text Within a Cell Without Drawing Tools ????

[chelseasikoebs]

I can't find anywhere where there may be some VB code to allow text within a cell in Excel to be circled. I don't want drawing objects, because many times I need to modify 30-40 sheets at one time and Excel doesn't allow you to alter drawing objects when you have multiple sheets selected. Therefore, I just want to be able to apply a circle around some text in a cell the same way you would underline it, for example. Is there ANY way to do this?? I figured my only hope is VBScript?? Thank you!!!

 

[Jonmo1]

There is no way to circle text in a cell (without drawing objects).

The only possible method I could possibly imagine, is to search the internet for a FONT that has the Letters Circled. Then you could use VBA to change to use that font on the specific character you want circled.

However, that would circle each letter, not necessarily a whole string...

 

[chelseasikoebs]

What a bummer!! Thanks for the help, though!

 

[Jeshua]

It may be able to be done using conditional formatting. Do you have certian text in mind or just any text?

 

[chelseasikoebs]

There is actually a listing of 6- or 7-digit numbers and when we fill out this form, we need to circle one or more of them.

 

[Jeshua]

Do you have a list of the numbers and what sort of other information is on the sheet you could set up conditional formatting to put a square around a cell when that number appears there. However I think maybe conditional formatting may not be advanced enough for what you need. But it still may be worth a try

 

[Jaafar Tribak]

many times I need to modify 30-40 sheets at one time and Excel doesn't allow you to alter drawing objects when you have multiple sheets selected

If you know the cells whose text needs drawing why not automate the task via a macro?

Regards.

 

[Tom Urtis]

I just want to be able to apply a circle around some text in a cell the same way you would underline it, for example. Is there ANY way to do this??

Yes there is a way to circle text without involving drawing shapes or VBA.

Let's say in column B you are entering text values, such as North, South, East, and West. Any text values will do, this is just an example.

Let's say the text you want circled is South, meaning any cell with South as its text value will be circled.

Select the range in column B where you are working, say B1:B30. With that range selected, go to Data Validation and in the DV dialog on the Settings tab, select Custom from the Allow field drop-down.

In the Formula field, enter = B1<>"South"

Important, on the Error Alert tab, deselect (take the checkmark out of the box next to) "Show error alert after invalid data is entered".

Click OK to exit the Data Validation dialog.

Now go ahead and enter your text in column B, and when you are ready to have non-Drawing Shape circles placed around South in B1:B30, select range B1:B30.

From the worksheet menu click on Tools > Formula Auditing > Show Formula Auditing Toolbar.

If you mouse over the icons in that toolbar, you'll see that the 4th icon from the right will have a tooltip that says "Circle invalid data".

Click on that icon and the cells with "South" in selected range B1:B30 will be circled in red.

You can have the Formula Auditing toolbar float on your page if you'll be needing it often for this, but in any case that's how to accomplish what you are asking, without VBA or drawing tools.

You can accomplish the same thing with numbers, example instead of =B1<>"South" it can be =B1<>50 and any cell containing 50 will be circled.

 

[Jaafar Tribak]

Yes there is a way to circle text without involving drawing shapes or VBA.

That's a handy functionality indeed !! I never knew about it. I guess it can be used in other scenarios too.

Thanks a lot for that Tom .

Regards.

 

[Tom Urtis]

Thanks Jaafar !