Finding cells with a value ending in '.0'

[MDP420]

Hi

I have a spreadsheet with a series of values (140.0, 140.2 etc) and I want
to locate and isolate all cells ending in '.0'

When I search for cells with this value using CTRL F however, it doesn't
recognise .0 or *.0 or ?.0

Just wanted to know if there's a quick way of doing this otherwise it's
going to be a long manual job!!

Thanks

 

[Harlan Grove]

I have a spreadsheet with a series of values (140.0, 140.2 etc) and I
want to locate and isolate all cells ending in '.0'

When I search for cells with this value using CTRL F however, it
doesn't recognise .0 or *.0 or ?.0

....

If these are numbers simply formatted with a single decimal to the
right of the decimal point, then the '.0' is only part of the cell's
display, not it's value or formula. Excel's Edit > Find can only
search through cells' values or formulas, not what they display.

You could use macros to locate such cells in the selected range or
active worksheet. There's also a way to select all such cells, if
that's of any use.

1. Insert a blank worksheet just before or just after the worksheet
you want to search. That blank worksheet will then be active.

2. Activate the worksheet you want to search.

3. Hold down a [Shift] key and click on the tab for the blank
worksheet you added. The worksheet you want to search should still be
the active worksheet, but the other worksheet should also be selected.

4. Select the range you want to search. Both worksheets should be
selected, so Excel selects the same range in the blank worksheet.

5. Click on the blank worksheet's tab. That should activate the blank
worksheet AND unselect the worksheet you want to search.

6. If the first cell in the selected range were B3, type a formula
like =1/(OtherSheet!B3=INT(OtherSheet!B3)) and press [Ctrl]+[Enter].
This will enter similar formulas in all selected cells, where similar
means the same as if you had copied cell B3 and pasted the formula
into all other selected cells.

7. Hold down a [Shift] key and click on the tab for the worksheet you
want to search. The formerly blank worksheet should still be active
and the range of newly entered formulas still selected, but the
worksheet you want to search should now also be selected.

8. Press [F5] to display the Go To dialog, click on the Special...
button, select Formulas and uncheck all options below it EXCEPT
Numbers, and click OK. Only cells containing numbers should be
selected, and those correspond to cells containing integers (i.e.,
with fractional part .0) in the worksheet you want to search. Excel
will select the same cells in the worksheet you want to search.

9. Click on the tab for the worksheet you want to search to activate
it and deselect the formerly blank worksheet. The cells containing
integers should all be selected, though the selection may span many
different areas.

 

[Harlan Grove]

=MOD(A2,1)=0

....

Not that the OP necessarily has values of the following magnitude, but
try this with A2 containing 134217728 (2^27).

 

[Harlan Grove]

When I search for cells with this value using CTRL F however, it
doesn't recognise .0 or *.0 or ?.0

....

Another alternative is to use conditional formatting. Select the range
you want to search. I'll assume the top-left cell of that range is B3.
Run the menu command Format > Conditional Formatting, select Formula,
enter the formula =B3=INT(B3), and select a jarring format - highly
contrasting background colors usually bese.

 

[Max]

With data in A2 down
Try in B2, copied down: =IF(A2="","",INT(A2)=A2)
Then autofilter col B for TRUE
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,700 Files:353 Subscribers:53
xdemechanik

 

[Rick Rothstein \(MVP - VB\)]

If these are numbers simply formatted with a single decimal to the

right of the decimal point, then the '.0' is only part of the cell's
display, not it's value or formula. Excel's Edit > Find can only
search through cells' values or formulas, not what they display.

Not exactly. If you show all the options (using the Options>> button if
necessary), type .0 (that is dot-zero) in the "Find what:" field, click the
Format button and select the same format that the cells are formatted to,
and select Values in the "Look in:" drop down, then the Find All button will
locate all the cells ending in .0 (dot-zero).

Rick

 

[Rick Rothstein \(MVP - VB\)]

Note: The following assumes you have your cells formatted to display a
single decimal digit.

In case you didn't read my response to Harlan, or if that response was too
brief to make sense of, give this a try. Bring up the Find dialog box (using
either Edit/Find from the menu bar or pressing Ctrl+F on the keyboard). If
all the options are not showing, click the Options>> button. Type .0 (that
is, a dot followed by a zero) in the "Find what" field. Next, click the down
arrow on the Format button and select "Choose Format From Cell" on the
drop-down list that appears; then click any cell with your cell-formatted
value in it. Alternately, you could click the Format button, click the
Number tab and select the same cell number format you used to format the
cells initially. After you have done that, select Values in the "Look in"
drop-down back on the Find dialog box. That's it... click the "Find All"
button and you should see all your cells where the value ends with .0
displayed.

Rick