Search and Replace

[bruce roberson]

I would like to search a column with formulas similar to
the one below.

=ROUND([NM200307.xls]Data!G78*40/320*0.975,0)

What I want to do without editing each cell is basically
this:

=ROUND(40/320*0.975,7)

So, in this case I want to remove everything inside the
round up to the fractional part which is right after
the "*" in each case. Then, instead of rounding this
result to whole numbers with ",0", I want to replace it
with ",7".

The exact references to the left side will of course vary,
but the ,0 to ,7 will remain constant.

So, can anyone come up with a find and replace that will
do the trick?

Thanks,

Bruce

[J.E. McGimpsey]

First run replace:

Find What: [*~*
Replace with: <leave blank>

then again

Find What: ,0
Replace with ,7

In article <0c0e01c3770e$e99269c0$a001...@phx.gbl>,

[Bruce Roberson]

I had already done the second replace like that. But for
the first one find "[*~*", it keeps saying the name is not
valid. What name... I don't know what name it is referring
to.

>.
>

[Dave Peterson]

J.E.'s suggestion worked fine for me.

But maybe your data wasn't exactly what you described????

If you change
=
to
$$$$$ (some unique set of characters)

then your formulas will be converted to text.

Then try J.E.'s suggestion (both of them). Except for the $$$$$, does those
look like valid formulas?

If yes, change those $$$$$ back to =.

If no, then maybe it was some difference in your formula from what you posted.

--

Dave Peterson
ec3...@msn.com

[Judy]

Is there a way in Excel to do a replace that will apply
bold to text, the way you can in Word. Is there a code I
would use in the Replace field to signify bold. Thanks

[Dave Peterson]

Not built into excel. You could use a macro, though:

Option Explicit
Sub testme01()

Dim myWords As Variant
Dim myCell As Range
Dim myRng As Range
Dim FirstAddress As String
Dim iCtr As Long
Dim letCtr As Long

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells _
.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

myWords = Array("test", "bold", "hilight")

If myRng Is Nothing Then
MsgBox "No Text Cells found in Selection"
Exit Sub
End If

For iCtr = LBound(myWords) To UBound(myWords)
With myRng
Set myCell = .Find(What:=myWords(iCtr), After:=.Cells(1), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not myCell Is Nothing Then
FirstAddress = myCell.Address
Do
For letCtr = 1 To Len(myCell.Value)
If StrComp(Mid(myCell.Value, letCtr, _
Len(myWords(iCtr))), _
myWords(iCtr), vbTextCompare) = 0 Then
myCell.Characters(Start:=letCtr, _
Length:=Len(myWords(iCtr))) _
.Font.FontStyle = "Bold"
End If
Next letCtr

Set myCell = .FindNext(myCell)

Loop While Not myCell Is Nothing _
And myCell.Address <> FirstAddress
End If

End With
Next iCtr

End Sub

You can just put the words you need to highlight in this line:

myWords = Array("test", "bold", "hilight")

(one word is ok, too.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--

Dave Peterson
ec3...@msn.com

[Judy]

Thanks, I will give it a try.

>.
>

[Judy]

Thanks so much this worked perfectly.

> myCell.Characters
(Start:=letCtr, _
> Length:=Len
(myWords(iCtr))) _
> .Font.F

>.
>