Is there any way to get the Find function to locate numeric values?
i.e. cells are formatted as Numbers with a comma separator for thousands and brackets for
negative values.
ex 1,079.00
A search for 79.00 returns any cell with 79.00 (179.00), 1,079.00 etc.

If I try an exact match (Match Entire Cell Contents) it finds nothing - I'm assuming because of
the sign byte.

--
Regards
Rob

Try this:
=ISERROR(FIND(7,A1)=FIND(9,A1,FIND(7,A1)+1)-1)=FALSE

"RobWN" wrote:

Is there any way to get the Find function to locate numeric values?
i.e. cells are formatted as Numbers with a comma separator for thousands and brackets for
negative values.
ex 1,079.00
A search for 79.00 returns any cell with 79.00 (179.00), 1,079.00 etc.

If I try an exact match (Match Entire Cell Contents) it finds nothing - I'm assuming because of
the sign byte.

--
Regards
Rob

Thanks Tevuna;
I know I can write a function to do it but what I was really asking is there anything I can do
with the built in "Find" function.
--
Regards
Rob
"Tevuna" wrote in message
...
Try this:
=ISERROR(FIND(7,A1)=FIND(9,A1,FIND(7,A1)+1)-1)=FALSE

"RobWN" wrote:

Is there any way to get the Find function to locate numeric values?
i.e. cells are formatted as Numbers with a comma separator for thousands and brackets for
negative values.
ex 1,079.00
A search for 79.00 returns any cell with 79.00 (179.00), 1,079.00 etc.

If I try an exact match (Match Entire Cell Contents) it finds nothing - I'm assuming because
of
the sign byte.

--
Regards
Rob

Wasn't I using the built-in FIND funciton?
You can't search for more than one character at once, and you can't search
for a number value. Just characters

"RobWN" wrote:

Thanks Tevuna;
I know I can write a function to do it but what I was really asking is there anything I can do
with the built in "Find" function.
--
Regards
Rob
"Tevuna" wrote in message
...
Try this:
=ISERROR(FIND(7,A1)=FIND(9,A1,FIND(7,A1)+1)-1)=FALSE

"RobWN" wrote:

Is there any way to get the Find function to locate numeric values?
i.e. cells are formatted as Numbers with a comma separator for thousands and brackets for
negative values.
ex 1,079.00
A search for 79.00 returns any cell with 79.00 (179.00), 1,079.00 etc.

If I try an exact match (Match Entire Cell Contents) it finds nothing - I'm assuming because
of
the sign byte.

--
Regards
Rob

My apologies.
I should have said command (vs Function) - it's been a long day..
Thanks.

--
Regards
Rob
"Tevuna" wrote in message
...
Wasn't I using the built-in FIND funciton?
You can't search for more than one character at once, and you can't search
for a number value. Just characters

"RobWN" wrote:

Thanks Tevuna;
I know I can write a function to do it but what I was really asking is there anything I can
do
with the built in "Find" function.
--
Regards
Rob
"Tevuna" wrote in message
...
Try this:
=ISERROR(FIND(7,A1)=FIND(9,A1,FIND(7,A1)+1)-1)=FALSE

"RobWN" wrote:

Is there any way to get the Find function to locate numeric values?
i.e. cells are formatted as Numbers with a comma separator for thousands and brackets for
negative values.
ex 1,079.00
A search for 79.00 returns any cell with 79.00 (179.00), 1,079.00 etc.

If I try an exact match (Match Entire Cell Contents) it finds nothing - I'm assuming
because
of
the sign byte.

--
Regards
Rob

"RobWN" wrote...
I know I can write a function to do it but what I was really asking is
there anything I can do with the built in "Find" function.
....

You're using ambiguous (or just plain wrong) terminology. You seem to mean
the Edit Find command. Yes, at least in Excel 2003 and presumably 2007 and
maybe in earlier versions, but it requires using options. Specifically, to
find the integer numeric value 79, you need to change the 'Look in' option
to Values, you need to check 'Match entire cell contents', and you need to
enter 79 (no sign character, no decimal point or deciman places) as 'Find
what'.

I shouldn't post when I'm tired!
Sorry for the confusion and thanks.
Using xl'03
Yes - using the EditFind command
I have already tried what you suggest but it didn't work.

I have a column of cells containing dollar amounts, formatted, as I mentioned, as Numbers, two
decimal places with a "," thousands separator.
In this column are cells such as 79.00, (79.00), 1,079.00, 179.00 etc.etc..

With the "Look In" parameter set as "Values";
If I use a search argument of 79.00 - I get all occurrences of the value (all the above
examples).
If simply 79 is used it gets hits on any occurrence of 79 (100.79-for ex)
In either case, if I match entire contents - I get nothing.

I assume therefore that this is a straight character search and to do what I want I'd have to
write my own search routine.

Anyway, thanks for your interest and your reminder to be precise.
--
Regards
Rob
"Harlan Grove" wrote in message
...
"RobWN" wrote...
I know I can write a function to do it but what I was really asking is
there anything I can do with the built in "Find" function.
...

You're using ambiguous (or just plain wrong) terminology. You seem to mean the Edit Find
command. Yes, at least in Excel 2003 and presumably 2007 and maybe in earlier versions, but it
requires using options. Specifically, to find the integer numeric value 79, you need to change
the 'Look in' option to Values, you need to check 'Match entire cell contents', and you need
to enter 79 (no sign character, no decimal point or deciman places) as 'Find what'.

You said the cells are formatted for 2 decimal places, parentheses for
negative values and thousands separators.... you did this using Format
Cells, right? If so, in Edit/Find, set Look In for Values, do **not** check
mark either match option but click the Format button and choose the same
cell formatting options you used on the cells originally. Typing 79.00 in
the Find What field and repeatedly clicking the Find Next button should take
you to the cells you want.

Rick


"RobWN" wrote in message
...
I shouldn't post when I'm tired!
Sorry for the confusion and thanks.
Using xl'03
Yes - using the EditFind command
I have already tried what you suggest but it didn't work.

I have a column of cells containing dollar amounts, formatted, as I
mentioned, as Numbers, two decimal places with a "," thousands separator.
In this column are cells such as 79.00, (79.00), 1,079.00, 179.00
etc.etc..

With the "Look In" parameter set as "Values";
If I use a search argument of 79.00 - I get all occurrences of the value
(all the above examples).
If simply 79 is used it gets hits on any occurrence of 79 (100.79-for ex)
In either case, if I match entire contents - I get nothing.

I assume therefore that this is a straight character search and to do what
I want I'd have to write my own search routine.

Anyway, thanks for your interest and your reminder to be precise.
--
Regards
Rob
"Harlan Grove" wrote in message
...
"RobWN" wrote...
I know I can write a function to do it but what I was really asking is
there anything I can do with the built in "Find" function.
...

You're using ambiguous (or just plain wrong) terminology. You seem to
mean the Edit Find command. Yes, at least in Excel 2003 and presumably
2007 and maybe in earlier versions, but it requires using options.
Specifically, to find the integer numeric value 79, you need to change
the 'Look in' option to Values, you need to check 'Match entire cell
contents', and you need to enter 79 (no sign character, no decimal point
or deciman places) as 'Find what'.

If you're looking for a funciton, try this:
=FIND(7,A1)=FIND(9,A1,FIND(7,A1)+1)-1
Where TRUE contains 79 and #VALUE! and FALSE Doesn't


"RobWN" wrote:

Is there any way to get the Find function to locate numeric values?
i.e. cells are formatted as Numbers with a comma separator for thousands and brackets for
negative values.
ex 1,079.00
A search for 79.00 returns any cell with 79.00 (179.00), 1,079.00 etc.

If I try an exact match (Match Entire Cell Contents) it finds nothing - I'm assuming because of
the sign byte.

--
Regards
Rob

The following macro will select all cells that display 79.00

Sub find_79()
v = "79.00"
Dim r As Range
Dim rf As Range
Set rf = Nothing
For Each r In ActiveSheet.UsedRange
If r.Text = v Then
If rf Is Nothing Then
Set rf = r
Else
Set rf = Union(rf, r)
End If
End If
Next
If rf Is Nothing Then Exit Sub
rf.Select
End Sub
--
Gary''s Student - gsnu200740


"RobWN" wrote:

Is there any way to get the Find function to locate numeric values?
i.e. cells are formatted as Numbers with a comma separator for thousands and brackets for
negative values.
ex 1,079.00
A search for 79.00 returns any cell with 79.00 (179.00), 1,079.00 etc.

If I try an exact match (Match Entire Cell Contents) it finds nothing - I'm assuming because of
the sign byte.

--
Regards
Rob

Thanks.

--
Regards
Rob
"Gary''s Student" wrote in message
...
The following macro will select all cells that display 79.00

Sub find_79()
v = "79.00"
Dim r As Range
Dim rf As Range
Set rf = Nothing
For Each r In ActiveSheet.UsedRange
If r.Text = v Then
If rf Is Nothing Then
Set rf = r
Else
Set rf = Union(rf, r)
End If
End If
Next
If rf Is Nothing Then Exit Sub
rf.Select
End Sub
--
Gary''s Student - gsnu200740


"RobWN" wrote:

Is there any way to get the Find function to locate numeric values?
i.e. cells are formatted as Numbers with a comma separator for thousands and brackets for
negative values.
ex 1,079.00
A search for 79.00 returns any cell with 79.00 (179.00), 1,079.00 etc.

If I try an exact match (Match Entire Cell Contents) it finds nothing - I'm assuming because
of
the sign byte.

--
Regards
Rob