Find and Replace - wrong result

  • Thread starter Thread starter brianmiller
  • Start date Start date
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)
 
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
 
Back
Top