Find and Replace - wrong result

B

brianmiller

Having prduced a list of values from using the & concatenate method, I
want to end up with a list of only those which have a positive (over 0)
number. So I have:

(155), (111), (51), (0), (0), (0), (0), (0), (0), (0)

for example. What I have tried is to do a find and replace finding ",
(0)" and replacing it with nothing. Works great, until I look at the:

(66), (0), (0), (0), (0), (0), (0), (0), (0), (0)

values. Doing the replace I end up with:

-66

Or with:

(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)

I end up with:

0

(OK this one is easy to delete simply filtering on 0 and deleting them.


Any ideas how to get round this one please. I have tried many
variations of Find and Replace but all end up with -66 or -133 etc?

Brian
(Brian Miller, Data Officer, BBOWT)
 
D

Dave Peterson

When you did the find and replace, it was like reentering the value by typing
the new value.

And excel sees (66) as a negative number (Kind of an accounting format).

And excel sees (0) as 0.

I don't know a way to change this behavior with Find and Replace.

But you could select the range to fix and run a macro that does the fixing (and
formats the cell as text) before writing back to the cell:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim myStr As String

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
myStr = .Value
myStr = Application.Substitute(myStr, ", (0)", "")
.NumberFormat = "@" 'text
.Value = Trim(myStr)
End With
Next myCell

End Sub

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