Find and Replace

[lostsoul62]

Excel 2007 - I would like to do a Find and Replace a word and when I do it changes the entire cell by taking the words which have color and truning them black and changing my fonts to one size. How can I just Find and Replace just one word in a cell? Is there anyway I can do a find and have all the words highlighted like when you do a find on the Internet?

 

[Hikertrash]

Excel 2007 - I would like to do a Find and Replace a word and when I do it changes the entire cell by taking the words which have color and truning them black and changing my fonts to one size. How can I just Find and Replace just one word in a cell? Is there anyway I can do a find and have all the words highlighted like when you do a find on the Internet?

I'm not sure if you can highlight the word but you can change the color of the font, shade the cell etc. Find & select, replace, format, text & fill.

 

[ignatzatsonic]

Not clear from your explanation what you are trying to do. Find and replace has some options as you see when you go the that window.

More generally, have you looked into "conditional formatting"??

I use it to turn certain cells red or green, depending on whether the value in those cells is greater or less than a particular value found elsewhere in the worksheet. I think it can control font and font size as well, but I use it only for colors.

I'd assume you could force a cell to have a 24 point New Times Roman bolded font with a yellow background if the first word in the cell begins with J, etc if that is what you wanted. Conditional formatting has a lot of control. You make a series of "rules" and if what is in the cell matches the rule, the formatting changes. If not, it doesn't.

 

[lostsoul62]

The word and not the cell

I don't want to turn the cell into anything I want to turn a word in the cell to something different. In my case I want to turn a thousand words in a thousand cells which will be one word per cell that I am try to change ONLY. Is there anyway I can do this?

 

[clunkfish]

By chance, I came across a possible answer to this difficult problem (it's at Superscripts in Find and Replace (Microsoft Excel)).

That specific example talks about making one character in a cell superscript, but the macro they give could be modified to alter just one word. The limitation would be that the word would need to be the same, and in the same place, in every cell. The macro they give is:

Sub DoConvert()
Dim c As Range

For Each c In Selection.Cells
If c.Value = "yd2" Then
c.Characters(3, 1).Font.Superscript = True
End If
Next
End Sub

Say you had an Excel worksheet with thousands of cells, about a thousand of which contain the string "SevenForums is great". If you wanted to change the word "great" to bold, making those cells say "SevenForums is great", you could run this macro:

Sub DoConvert()
Dim c As Range

For Each c In Selection.Cells
If c.Value = "SevenForums is great" Then
c.Characters(16, 5).Font.Bold = True
End If
Next
End Sub

The (16, 5) bit tells the macro to go to the 16th character in the string, and to do the conversion on 5 characters. I've tried it and it does work - hopefully it will solve your problem. If you need any help with tweaking it, let us know

 

[Mike Connor]

Try these;

Advanced Find and Replace in Excel 2007 - For Dummies

How to find & replace all instances of a word/number in Excel | Video « Wonder How To

Regards....Mike Connor

 

[ihopnavajo]

I had the same problem and the solution presented here and elsewhere didn't quite solve what I looking for (the above solution only matches on the whole text string, as opposed to a substring and the other only formatted a substring in one cell), so I combined the two:

Sub Bold_substring()
Dim c As Range
Dim Start As Long
Dim TargetWord As String



For Each c In Selection.Cells

Start = 0
TargetWord = "substring I'm looking for"
Do
Start = InStr(Start + 1, c.Text, TargetWord)
If Start < 1 Then Exit Do
With c.Characters(Start, Len(TargetWord)).Font

If IsNull(FontBold) = False Then .Bold = True

End With

Loop

Next
End Sub