Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Font within Cell
Is it possible to replace only specific characters/text within a cell? For
example I have several cells that need to have one word in italtics, but when I use find and replace, all words in the cell are changed to italics. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Font within Cell
That's true.
You will need to reapply the formatting after the replace. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Stephanie M" wrote: Is it possible to replace only specific characters/text within a cell? For example I have several cells that need to have one word in italtics, but when I use find and replace, all words in the cell are changed to italics. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Font within Cell
Reapply the formatting - not sure I follow. Can I change only one word in the
cell without them all changing? "Wigi" wrote: That's true. You will need to reapply the formatting after the replace. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Stephanie M" wrote: Is it possible to replace only specific characters/text within a cell? For example I have several cells that need to have one word in italtics, but when I use find and replace, all words in the cell are changed to italics. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Font within Cell
On Tue, 28 Oct 2008 11:24:01 -0700, Stephanie M
wrote: Is it possible to replace only specific characters/text within a cell? For example I have several cells that need to have one word in italtics, but when I use find and replace, all words in the cell are changed to italics. If you have entered the text in the cell directly, select the cell select the particular characters within the cell (highlight them) right-click and select the font changes Note that the data MUST be TEXT. If the text string is the result of a formula, you cannot do this. This can also be done with a VBA Macro. --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Font within Cell
Thanks. I have a lot of cells to change. The only way I how to create a Macro
is to have it copy the actions. "Ron Rosenfeld" wrote: On Tue, 28 Oct 2008 11:24:01 -0700, Stephanie M wrote: Is it possible to replace only specific characters/text within a cell? For example I have several cells that need to have one word in italtics, but when I use find and replace, all words in the cell are changed to italics. If you have entered the text in the cell directly, select the cell select the particular characters within the cell (highlight them) right-click and select the font changes Note that the data MUST be TEXT. If the text string is the result of a formula, you cannot do this. This can also be done with a VBA Macro. --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Font within Cell
hi
to accomplish this.... in the formula bar, highlight the word you wish to reformat. with the word highlighted, right click the formula bar, select format cells. you will notice that only part of the normal format dialog is available, only that which pertains to text. apply whatever format you wish. you now have multiply formats in 1 cell. this applys to text only. you can not have multiple formats for numbers. regards FSt1 "Stephanie M" wrote: Reapply the formatting - not sure I follow. Can I change only one word in the cell without them all changing? "Wigi" wrote: That's true. You will need to reapply the formatting after the replace. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Stephanie M" wrote: Is it possible to replace only specific characters/text within a cell? For example I have several cells that need to have one word in italtics, but when I use find and replace, all words in the cell are changed to italics. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Font within Cell
On Tue, 28 Oct 2008 12:17:01 -0700, Stephanie M
wrote: Thanks. I have a lot of cells to change. The only way I how to create a Macro is to have it copy the actions. Is there some characteristic of the letters that need to be changed that we could test for? 1. Range to search: 2. How to determine which letters to change: 3. What to change them to: 4. What are the baseline characteristics of the font before any changes: --ron |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace Font within Cell
You could use a macro that finds the cell with the word and then italices the
characters in that word in that cell. If you want to try: Option Explicit Sub testme() Dim myRng As Range Dim FoundCell As Range Dim FirstAddress As String Dim myWords As Variant Dim wCtr As Long Dim wks As Worksheet Dim StartPos As Long Set wks = Worksheets("sheet1") 'change this to the list of words to find myWords = Array("qwer", "asdf") With wks 'change this to the range that should be inspected Set myRng = .Range("L:L") With myRng For wCtr = LBound(myWords) To UBound(myWords) FirstAddress = "" With .Cells Set FoundCell = .Find(What:=myWords(wCtr), _ after:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then 'do nothing, it wasn't found MsgBox myWords(wCtr) & " wasn't found!" Else FirstAddress = FoundCell.Address Do StartPos = InStr(1, FoundCell.Value, _ myWords(wCtr), vbTextCompare) If StartPos = 0 Then 'this shouldn't happen, 'since the .find worked ok Else With FoundCell.Characters _ (Start:=StartPos, _ Length:=Len(myWords(wCtr))).Font .FontStyle = "Italic" End With 'look for the next one Set FoundCell = .FindNext(after:=FoundCell) If FirstAddress = FoundCell.Address Then 'at the first address Exit Do End If End If Loop End If End With Next wCtr End With End With End Sub If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Stephanie M wrote: Is it possible to replace only specific characters/text within a cell? For example I have several cells that need to have one word in italtics, but when I use find and replace, all words in the cell are changed to italics. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace: find part cell, replace whole cell | Excel Worksheet Functions | |||
Can I replace a ' at the beginning of a text cell using Replace | Excel Discussion (Misc queries) | |||
Find and Replace without changing font | Excel Discussion (Misc queries) | |||
Excel - create button to replace cell content with cell value | Excel Worksheet Functions | |||
Find and replace of word causes change of font formatting | New Users to Excel |