Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2007

Tip: Looking for answers? Try searching our database.

Replace font of one character in a string

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
PaulS - 13 Jan 2007 00:35 GMT
I need to replace the font of one character in a text string in a range of
cells.
I can do this character by character by hand but I want to automate it.
If I use the replace function for one character than the font change is
applied to all characters in the cell while the font style (e.g. bold to
italic) is limited to the one specified character.
Does anyone know how to change the the font  for only one character in such
a way that I can automate it. (e.g. All arial "1"  need to be replaced by
windings "1 with a circle around it")
Ron Rosenfeld - 13 Jan 2007 01:18 GMT
>I need to replace the font of one character in a text string in a range of
>cells.
[quoted text clipped - 5 lines]
>a way that I can automate it. (e.g. All arial "1"  need to be replaced by
>windings "1 with a circle around it")

You can do it with a simple macro.

To enter the macro,

<alt-F11> opens the VB Editor.

Ensure your project is selected in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

Read the notes in the code.

You can use it by selecting the area where you want this to occur, then
executing the macro.

===========================================
Option Explicit

Sub WingdingReplace()
Dim c As Range
Dim sWD1 As String
Dim i As Long

sWD1 = Chr(129) 'wingding encircled 1

'can only apply this to text strings, and not to strings that
'are the result of formulas.

'This does not work on formulas.
'If you want it to do so, you will have to also replace
' the formula with a plain text string.

For Each c In Selection
   If c.HasFormula = False Then
       c.Value = Replace(c.Text, "1", sWD1)
           For i = 1 To Len(c.Text)
               If Mid(c.Text, i, 1) = sWD1 Then
                   With c.Characters(Start:=i, Length:=1).Font
                       .Name = "Wingdings"
'this line may not be necessary but looked better on my screen
                       .Size = .Size + 2
                   End With
               End If
           Next i
   End If
Next c

End Sub
=========================================
--ron
jchen - 13 Jan 2007 01:41 GMT
Or you can do a dirty way by hand. Copy the range and paste them in a new
sheet. And use the replace all function. And Copy and paste it back.

If you have formula included in the sheet.
Copy the whole sheet to a new one, replace all, and only copy the range and
paste it back.
Ron Rosenfeld - 13 Jan 2007 03:45 GMT
>Or you can do a dirty way by hand. Copy the range and paste them in a new
>sheet. And use the replace all function. And Copy and paste it back.
>
>If you have formula included in the sheet.
>Copy the whole sheet to a new one, replace all, and only copy the range and
>paste it back.

Did you try your solution?

When I tried it, it did not change the font, but rather displayed a small box.

--ron
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.