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

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

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

"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'.

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

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'.

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

Rick;
Thanks
I tried this, thinking that was how it determined the format of the values (character vs.
numeric) but it didn't work.

Just to be sure;
Format of cells in the range being searched - Number, 2 places of decimal, thousands separator
and brackets for negative (Red).
Search In -Values
Other options - none
From the Format dropdown selected "Choose Format From Cell" and then selected a representative
cell.
(When I selected the Format that the "Preview *" is displayed (preview cannot be shown) in the
adjacent box.

Find What = 79.00
Find Next (repeatedly).

It returns all values with 79.00 vs. just those that are = 79.00.

If this is functioning as designed then it's just something that I'll have to remember and pass
on to those in our organization who are effected.
So I guess that's my question, is this the way it's supposed to work?

Again, thanks.
--
Regards
Rob
"Rick Rothstein (MVP - VB)" wrote in message
...
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'.

I think I misunderstood what you were looking to do. You **only** want to
find the value 79.00 **exactly**. I don't think you can do that directly;
however, if (after you set everything up as I described), you click the Find
All button (instead of the Find Next button), you will get a list of all the
cells that pressing Find Next returns one at a time. You can click on the
value that you want in the list to select that cell and then just close the
dialog box. I don't think there is a more direct method than that.

Rick


"RobWN" wrote in message
...
Rick;
Thanks
I tried this, thinking that was how it determined the format of the values
(character vs. numeric) but it didn't work.

Just to be sure;
Format of cells in the range being searched - Number, 2 places of decimal,
thousands separator and brackets for negative (Red).
Search In -Values
Other options - none
From the Format dropdown selected "Choose Format From Cell" and then
selected a representative cell.
(When I selected the Format that the "Preview *" is displayed (preview
cannot be shown) in the adjacent box.

Find What = 79.00
Find Next (repeatedly).

It returns all values with 79.00 vs. just those that are = 79.00.

If this is functioning as designed then it's just something that I'll have
to remember and pass on to those in our organization who are effected.
So I guess that's my question, is this the way it's supposed to work?

Again, thanks.
--
Regards
Rob
"Rick Rothstein (MVP - VB)" wrote in
message ...
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'.

Rick;
Thanks, finding the exact match is what I was looking for.

I was looking for way to "run" through the sheet by simply clicking the Find Next (or Alt-F)
rather than scrolling in the Find All results.
Normally this isn't a huge concern, it's just that this particular use involves about 3,000 -
9,000 rows per month (bank reconciliation) and it would have been simpler if it could find a
direct match on the Find Next.

In the end you've answered my question-I'm always wondering if I'm missing something, many
thanks.

--
Regards
Rob
"Rick Rothstein (MVP - VB)" wrote in message
...
I think I misunderstood what you were looking to do. You **only** want to find the value 79.00
**exactly**. I don't think you can do that directly; however, if (after you set everything up
as I described), you click the Find All button (instead of the Find Next button), you will get
a list of all the cells that pressing Find Next returns one at a time. You can click on the
value that you want in the list to select that cell and then just close the dialog box. I don't
think there is a more direct method than that.

Rick


"RobWN" wrote in message ...
Rick;
Thanks
I tried this, thinking that was how it determined the format of the values (character vs.
numeric) but it didn't work.

Just to be sure;
Format of cells in the range being searched - Number, 2 places of decimal, thousands
separator and brackets for negative (Red).
Search In -Values
Other options - none
From the Format dropdown selected "Choose Format From Cell" and then selected a
representative cell.
(When I selected the Format that the "Preview *" is displayed (preview cannot be shown) in
the adjacent box.

Find What = 79.00
Find Next (repeatedly).

It returns all values with 79.00 vs. just those that are = 79.00.

If this is functioning as designed then it's just something that I'll have to remember and
pass on to those in our organization who are effected.
So I guess that's my question, is this the way it's supposed to work?

Again, thanks.
--
Regards
Rob
"Rick Rothstein (MVP - VB)" wrote in message
...
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'.

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

"Rick Rothstein (MVP - VB)" wrote...
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.
....
"RobWN" wrote in message
....
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.
....

With the following in A1:A8 (cell contents, i.e., .Formula properties rather
than .Text properties),

0
0.79
1.79
79
79.01
179
1079
1079.79

I run Edit Find, I enter

79

as 'Find what', I change 'Look in' to Values, ***AND*** I check 'Match
entire cell contents', then I click Find All, and Excel shows the *ONLY*
match as cell A4, the only one that contains just 79.

If when you say 79.00, you're referring to how the cell value APPEARS, then
if there's truly no fractional part, its VALUE will be just 79. However, if
there were a fractional part so that the number were either

78.9999999999997

or

79.0000000000001

but just appears as 79.00, then you'd also need to use the Format option,
selecting the same format as used in the cells you want to find (so you'd
need to ensure they're all formatted the same), ***BUT*** you'd ***STILL***
need to have 'Match entire cell contents' checked. That last bit is the key.

With the following in A1:A8 (cell contents, i.e., .Formula properties
rather than .Text properties),

0
0.79
1.79
79
79.01
179
1079
1079.79

I run Edit Find, I enter

79

as 'Find what', I change 'Look in' to Values, ***AND*** I check 'Match
entire cell contents', then I click Find All, and Excel shows the *ONLY*
match as cell A4, the only one that contains just 79.

If I type in the values you listed above (so they are numbers, not text) and
format the cells as the OP said (2 decimal places, comma separator, red
parentheses for negative values); then if I start Edit/Find and enter 79 (or
any variation of it) in 'Find what', change 'Look in' to Values and check
'Match entire cell contents', and click Find All... Excel displays a dialog
box saying "Microsoft Office Excel cannot find the data you're searching
for."... Do you see something different?

Rick

"Rick Rothstein (MVP - VB)" wrote...
With the following in A1:A8 (cell contents, i.e., .Formula properties
rather than .Text properties),
....

[removing quoting angle brackets for clarity]
0
0.79
1.79
79
79.01
179
1079
1079.79

That is, these are all their respective cells' .Formula properties, so they
would also be the CStr results of their cells' .Value properties, and the
result of the formula =COUNT(A1:A8) is 8.

Then I number-format them as

#,##0.00

** This is the difference!! **

. . . then if I start Edit/Find and enter 79 (or any variation of it) in
'Find what', . . .

Enter

79.00

(5 characters in total) as 'Find what'

. . . change 'Look in' to Values and check 'Match entire cell contents',
and click Find All... Excel displays a dialog box saying "Microsoft Office
Excel cannot find the data you're searching for."... Do you see something
different?

Yes, using the simpler number format

#,##0.00

as both the number format for A1:A8 and the optional search format for the
Edit Find menu command, Excel 2003 returns a match for cell A4. However,
when I change the number format for both A1:A8 and the optional search
format to

#,##0.00_);[Red](#,##0.00)

I also get the dialog saying no matches found. **BUT** that's because
positive numbers would have single trailing spaces, so 'Find what' would
need to be changed to

[79.00 ]

without the square brackets, which I only included to highlight the
MANDATORY trailing space. When you add the trailing space, Excel finds cell
A4. The point here is when using optional search number formats, Edit Find
is searching cells' .Text properties, and your 'Find what' needs to conform
to what the cell would display.