Selecting specific characters in a cell

[MigrantFirmWorker]

I would like to apply formatting to the selected characters within a cell (as opposed to the entire cell). I have been unable to find a way to access the selected substring or even the cursor position. Any ideas?

 

[onlyadrafter]

Hello,

When you select the cell the contents appear in the formula bar, select the characters here, then try applying formats.

This will work on text, but I believe, cannot be done on formulas.



----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[MigrantFirmWorker]

Sorry. I neglected to mention that I am trying to do this with VBA.

 

[onlyadrafter]

Hello,

In which case which characters are you trying to change the format to and what format?



----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[MigrantFirmWorker]

I would like to write icon-executed macros that will subscript and superscript the selected characters in a cell (like the canned functionality in Word & PP). I know how to manipulate the text. I just haven't been able to determine which characters are selected.

 

[onlyadrafter]

Hello,

Are the characters selected constant i.e. is it the second and third cells?

Can you give some examples?


----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[MigrantFirmWorker]

The character(s) could be at any position in the string. For instance, if my cell contains:

"a generic string"

I would like to be able to highlight any subset of characters and superscript them with one click of an icon. Going to the Format Cells form is tedious (although I'm very good at it by now).

 

[jghrist]

You can use the Characters {expression.Characters(Start, Length)} property to change the font of certain characters, but how to get the Start and Length? For that matter, how do you start a macro while in the edit cell mode?

The following macro works if you know what Start and Length you need, but of course that is the problem.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
With Selection.Characters(3, 1).Font
.Subscript = True
End With
End Sub

 

[MigrantFirmWorker]

I always had a nagging feeling that Microsoft omitted that canned feature from Excel for a reason.

 

[MintJulep]

Just tossing out a half-baked idea...

Highlight the text of interest in the cell and copy to the clip board.

Paste into a text box in a VBA form.

From the form, do a search on the text in the cell to find the string that is in the text box. This should id the start and length.

Use jghrist's method from there.

 

[bltseattle]

I've been annoyed about the lack of this canned functionality, too.

Here's an idea, what if you added a "flag" character in the cell text, then when you execute the macro it finds & removes the flag, and super- or sub-scripts the following character (or characters between two flags).

For example, using "~" as a flag, maybe the cell text:

"Test~1"

would become

"Test1" where the "1" is sub/superscripted

The VBA code would search for the flag, remove it, and then implement something like jbhrist's code to change the font to super/subscript.

 

[MigrantFirmWorker]

I appreciate the thought y'all have put into this. Unfortunately, it would appear that control-1 and clicking super(or sub)script is still the quickest means to this end... Maybe in the next upgrade...

Thanks.