You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
I run Numbers for Mac. Version 6.2.1.
While searching for a string of letters I don't want to search the whole spreadsheet dokument, but rather a few columns on one of the tabs. How do I restrict the search to the cells of my preference?
You may find useful the attached Find in Selection Automator Quick Action (Dropbox download):
Doubleclick to install and, the first time, make sure at System Preferences > Security & Privacy > Privacy > Accessibility that Automator and Numbers are both listed and checked. You may also need to make sure Automator is checked under Numbers at System Preferences > Security & Privacy > Privacy > Automation.
Thereafter, to use, select the area of your table that you want to search (for example the first four columns) and choose Find in Selection from the Numbers > Services menu. The script will prompt you for a search term and then list the cells where that search term occurs. You can work from the list, or click one of the search 'hits' to go immediately to that cell.
SG
You may find useful the attached Find in Selection Automator Quick Action (Dropbox download):
Doubleclick to install and, the first time, make sure at System Preferences > Security & Privacy > Privacy > Accessibility that Automator and Numbers are both listed and checked. You may also need to make sure Automator is checked under Numbers at System Preferences > Security & Privacy > Privacy > Automation.
Thereafter, to use, select the area of your table that you want to search (for example the first four columns) and choose Find in Selection from the Numbers > Services menu. The script will prompt you for a search term and then list the cells where that search term occurs. You can work from the list, or click one of the search 'hits' to go immediately to that cell.
SG
Hi Ombenning,
To search all four columns, add a new column to bring them together. Then you can search that column for a text string.
Formula in E2 =A2&B2&C2&D
Formula in F2 =IFERROR(FIND(Find::$A$1,E2),0)
=OMFEL(HITTA(Find::$A$1;E2);0)
If you want to find A and a, use SEARCH (not case-sensitive)
Formula in F2 =IFERROR(SEARCH(Find::$A$1,E2),0)
=OMFEL(SÖK(Find::$A$1;E2);0)
Now you can apply a filter to Table 1
Regards,
Ian.
Hi Ombenning,
In Numbers, Menu > Edit > Find will always search the whole document.
You could copy the cells of your preference to a new Numbers document and do the find there.
There may be a better way. Use the FIND function in a formula to refer to a particular column of a table.
More information on your overall aim will help us to find a solution for you.
Regards,
Ian.
Good answer. Limiting search area is not included in the program per se.
Formula writing is tedious when not so experienced. What would the search result actually be? That yes, there is such a value (as the one asked for) somewhere in the desired matrix, namely at cell so and so (F225 as an example), and what if there are more than one occurrence, how would that be presented? Cell references stacked one after another as a string in the answer cell?
Or could one hope for that the formula would reveal its result by walking me to the result one after the other (as in Edit > Find when I click the "next occurrence" arrow (equal to cmd G). All within my preferred matrix of course.
This seems complicated. I might as well just do the search and accept that I need to toggle a dozen "finds" before I get to the one I look for. Excel had this simply: "Search within selection". But they don't support Mac any longer.
Hi Ombenning,
Are you trying to simply find each cell in a column that contains a string of characters?
Or do you want to find a string of characters within a cell and automatically replace that string with a different string of characters?
Both of the above are possible in Numbers.
I wrote: "More information on your overall aim will help us to find a solution for you."
I am not sure what you want to do.
Regards,
Ian.
Thank you again.
My aim is to look through a rather large dokument of some 3500 rows, 30 columns and a dozen tabs.
So I want to find a certain string of numbers thet may occur at several places in the dokument.
But it is solely in the four first columns of the first tab that I want to do the searching. Not in the rest of the dokument where there are other occurrences.
There is regularly only one occurrence in the 4 first columns, so all other finds are redundant.
What I look for is what is being noted on the rest of that very line where my found number resides.
Hope this is an understandable description. English is not my mother tongue. Swedish is.
Yours best
Ombenning
Thank you Ian!
I reckon there is no function that would actually take you to the desired position within the matrix (the limited matrix consisting of selected columns only), keeping all the cells displayed.
Instead introducing a filter could of course do the trick. Though - this spreadsheet contains tens of thousands of cells, and my poor Mac might not like to do that as a routine over and over again. It seems strenuous, but I could be wrong.
Nevertheless, I thank you very much for hinting about the formulas. That language is certainly worth getting comfortable in. Much to do ahead. Thank you, I believe we end the session here.
Well, it's been a few years now since this was up. Hope I recall correctly. Apple introduced its new Intel processor and I upgraded to one such Mac. My previous configuration included a copy of Excel, single application, no Office package. It had served well and had that feature; "search within selection".
But the new IOS couldn't handle that older Excel version and trying to upgrade, Microsoft announced that they would end supporting Office applications for the new Mac platforms.
So I settled with Numbers, that came with the Mac. It's not as sophisticated but it comes with the computer so I should not complain.
I haven't bothered to keep up with Microsofts policy, but if your question implies that Excel is back with Mac, it's fine with me. I am getting used to my Numbers (and Pages and Keynote...) including the export functions in case i need to save an Excel version, so things roll by. I't just that function I'm asking for that bothers me. A bit at least. Though I have gotten nice respons here as you can see.
Of course you could say that it wasn't Excel that withdrew from Apple, but rather Apple that outgrew Excel. I guess that just depends on your viewpoint..
Hi Ombenning,
Not too strong on the historical details myself, but I do know MS was back on the Mac with MS Office Mac 2011, and later released an upgrade to Mac Office 2016. Office 365, the online version, is also available for Mac.
I've not had much reason to purchase any of them for my own use, but we do have Office Mac installed on my spouse's iMac, for greater compatibility with Excel and Word, which she uses in connection with her community volunteer activities. Mine, with the same organizations, and others, are adequately served by Numbers' and Pages' ability to print to pdf and to export to Excel or Word formats.
Regarding "nice responses," those are quite common in these communities (perhaps especially so in the iWork applications communities). We don't rant, and do attempt to give useful answers to all questions received.
As for my question—not a complaint, just curious whether this was a still current situation in your view.
Regards,
Barry
BTW, just because "it comes with the computer" doesn't mean you can't '.complain.' Apple provides a Feedback channel with all (or most) of its software products, and a gateway to that channel in the Application menu of each application.
In Numbers, open the Numbers menu, choose Provide Numbers feedback, and make a feature enhancement request regarding ability to search a specified location rather than the whole document. You're unlikely to see quick action, but every nudge helps.
Meantime, SGIII, our currently most active Applescript and Automator guru, frequently provides one-click solutions to tasks not directly addressed in Numbers, including the one presented in this discussion.
Happy Christmas,
B
Hi SG,
That is a nice bit of work. Thanks.
quinn
HI Ombenning,
I'm puzzled by the second statement here: "Excel had this simply: "Search within selection". But they don't support Mac any longer."
Can you expand on it?
Regards,
Barry
Very nice. I checked it out.
Yours best. Thank you. Merry Christmas!
Ombenning
Excel did “pretty much die” on the Mac platform quite a few years back, a least in the sense that it became a very poor cousin of Excel for Windows.
Now Excel on the Mac is excellent.
SG
Thanks to all of you who engaged yourselves so much in this issue and who helped in resolving it.
I am very grateful.
Bye and Merry Christmas!
Ombenning (Sweden)
searchin columns
