Windows 7 Forums

Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Find and Replace

25 Dec 2010   #1
lostsoul62

Windows 7 x64 RC1, Vista Ultimate, XP Pro
 
 
Find and Replace

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?


My System SpecsSystem Spec
.

25 Dec 2010   #2
Hikertrash

Windows 7 Professional 32bit Sp1
 
 

Quote   Quote: Originally Posted by lostsoul62 View Post
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.
My System SpecsSystem Spec
25 Dec 2010   #3
ignatzatsonic

Microsoft Community Contributor Award Recipient

Windows 7 SP1, Home Premium, 64-bit
 
 

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.
My System SpecsSystem Spec
.


26 Dec 2010   #4
lostsoul62

Windows 7 x64 RC1, Vista Ultimate, XP Pro
 
 
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?
My System SpecsSystem Spec
06 Jan 2011   #5
clunkfish

Windows 7 Enterprise x64
 
 

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
My System SpecsSystem Spec
06 Jan 2011   #6
Mike Connor

Several, including Windows 7 x64 Ultimate
 
 

My System SpecsSystem Spec
07 Oct 2011   #7
ihopnavajo

windows xp
 
 

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
My System SpecsSystem Spec
Reply

 Find and Replace




Thread Tools





Similar help and support threads
Thread Forum
Excel 2010, find & replace
Hi guys, In my Excel 2010 workbook there is a cell with the value of "90" in type of number/general. When I go to find and replace dialog box and try to find the number "90" by typing it in Find what rectangle, then I want the program looks for it in formulas (look in: Formulas). But it shows...
Microsoft Office
Microsoft Office 2007 Excel Find and Replace Command
I know I can do this as I have done it in the past a long time ago. Using the Find and Replace option add and extension to my sku numbers, such as: SKU # 8686708052 I have a column of these sku's and want to add the extension .jpg to all of them at one time! 8686708052.jpg
Microsoft Office
Word Document find and replace, find duplicates
I have a document that is hundreds of pages long. Within this document there are certain sentences, phrases that repeat and I need to find them in a practical manner. Now I could go through manually, look for lines I suspect are repeated elsewhere and search for them, but that would take...
Microsoft Office
Where can I find midi driver to replace one deleted by mistake
I run my Dell XPS 17 in live performance using multiple midi devices. I had a problem of the midi ports available being filled do to doubled up instances and had to go into the registry to clean out the copied instances. I have done that before; but, this time I accidentally deleted the primary...
General Discussion
Notepad++ using find-replace function without reg. expressions??
I'm hoping to get lucky with this post since the forums on the official Notepad++ website are very slow to respond, and maybe someone on here uses NPP and knows the answer. Say I want to find a certain block of code that spans across multiple lines, and replace it with another block of code...
Software
Dreamweaver CS4 Find and Replace question
Is there any way to search and replace a term in an entire directory? For example, I am working on getting my organization's new website ready to go live and there are tons of text changes that I need to propogate across the site. I am in the root directory (standupnew), and it has about 80...
Software

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 23:19.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App