[FRIAM] Find String in Spreadsheet

Sat Nov 26 16:49:38 EST 2022

If I export it to a CSV file I'll just put in on my Linux machine and use
grep.

---
Frank C. Wimberly
140 Calle Ojo Feliz,
Santa Fe, NM 87505

505 670-9918
Santa Fe, NM

On Sat, Nov 26, 2022, 2:36 PM Gary Schiltz <gary at naturesvisualarts.com>
wrote:

> Instead of digging into the guts of Excel with Visual Basic, I think
> it would be about as easy (and maybe more fun) to import the
> spreadsheet into a database that you could query. If I were to take a
> first crack at it, I would first export the Excel file to a comma
> separated values file (CSV file). Then install MySQL and MySQL
> Workbench. It wouldn't surprise me if there was a Workbench option to
> import a CSV file as a new table in the current database. Then you
> could write plain SQL queries to look for the data you want, or go as
> far as writing some Java code using JDBC to do the queries.
>
> Or instead of MySQL and Workbench, you could try LibreOffice Base (the
> database component) to accomplish the same thing. This would be the
> open source equivalent of using Microsoft Access.
>
> All this from memory and speculation, of course (isn't that what us
> FRIAM'ers are best at?)..
>
> Gary
>
> On Sat, Nov 26, 2022 at 11:20 AM Frank Wimberly <wimberly3 at gmail.com>
> wrote:
> >
> > Thanks, Brent.
> >
> > Stanford published a spreadsheet with about 200,000 researchers.  Each
> row contains name, institution name, country, etc.  I want to be able to
> search for the data about a given individual, the people associated with an
> institution, etc.
> >
> > I used to program macros in VBA but I only did it for a few months and
> that was about 15 years ago.
> >
> > Frank
> > ---
> > Frank C. Wimberly
> > 140 Calle Ojo Feliz,
> > Santa Fe, NM 87505
> >
> > 505 670-9918
> > Santa Fe, NM
> >
> > On Sat, Nov 26, 2022, 9:03 AM Brent Auble <brent at auble.net> wrote:
> >>
> >> If you just want to find something, using the Find option from the menu
> will work and it should give you the option of searching within the current
> sheet or across all sheets in the workbook. If there are multiple results,
> it should pop up a docked window at the bottom of the spreadsheet to show
> everything it's found.
> >>
> >> However, if you want to do it programmatically, I'm not sure if any of
> the built-in functions (formulas) will handle that well, mostly because of
> the complexity of handling multiple results. Excel functions try really
> hard to just return single values so they can populate just the cell
> they're in. There are some exceptions, where a formula will run and put its
> results in multiple other cells, but those are uncommon because they can
> indiscriminately overwrite existing cell values.
> >>
> >> The is likely a capability in the Visual Basic language used for
> scripting Excel to handle searching, but it's not something I've used
> before (despite having done a good bit of Excel VBA in the past).
> >>
> >> What are you trying to accomplish?
> >>
> >> Thanks,
> >>
> >> Brent
> >>
> >>
> >>
> >> On Sat, Nov 26, 2022 at 10:42 AM, Frank Wimberly
> >> <wimberly3 at gmail.com> wrote:
> >> I used to know Excel pretty well but that was decades ago.  I want to
> find a given string, say "Wimberly" in a large spreadsheet.  How do I do
> that simply?  FIND function wants to provide the location within a larger
> string of a substring.  I want something like the Unix "grep' command.
> >>
> >> --
> >> Frank Wimberly
> >> 140 Calle Ojo Feliz
> >> Santa Fe, NM 87505
> >> 505 670-9918
> >>
> >> Research:  https://www.researchgate.net/profile/Frank_Wimberly2
> >> -. --- - / ...- .- .-.. .. -.. / -- --- .-. ... . / -.-. --- -.. .
> >> FRIAM Applied Complexity Group listserv
> >> Fridays 9a-12p Friday St. Johns Cafe  /  Thursdays 9a-12p Zoom
> https://bit.ly/virtualfriam
> >> to (un)subscribe http://redfish.com/mailman/listinfo/friam_redfish.com
> >> FRIAM-COMIC http://friam-comic.blogspot.com/
> >> archives:  5/2017 thru present
> https://redfish.com/pipermail/friam_redfish.com/
> >>   1/2003 thru 6/2021  http://friam.383.s1.nabble.com/
> >>
> >> -. --- - / ...- .- .-.. .. -.. / -- --- .-. ... . / -.-. --- -.. .
> >> FRIAM Applied Complexity Group listserv
> >> Fridays 9a-12p Friday St. Johns Cafe   /   Thursdays 9a-12p Zoom
> https://bit.ly/virtualfriam
> >> to (un)subscribe http://redfish.com/mailman/listinfo/friam_redfish.com
> >> FRIAM-COMIC http://friam-comic.blogspot.com/
> >> archives:  5/2017 thru present
> https://redfish.com/pipermail/friam_redfish.com/
> >>   1/2003 thru 6/2021  http://friam.383.s1.nabble.com/
> >
> > -. --- - / ...- .- .-.. .. -.. / -- --- .-. ... . / -.-. --- -.. .
> > FRIAM Applied Complexity Group listserv
> > Fridays 9a-12p Friday St. Johns Cafe   /   Thursdays 9a-12p Zoom
> https://bit.ly/virtualfriam
> > to (un)subscribe http://redfish.com/mailman/listinfo/friam_redfish.com
> > FRIAM-COMIC http://friam-comic.blogspot.com/
> > archives:  5/2017 thru present
> https://redfish.com/pipermail/friam_redfish.com/
> >   1/2003 thru 6/2021  http://friam.383.s1.nabble.com/
>
> -. --- - / ...- .- .-.. .. -.. / -- --- .-. ... . / -.-. --- -.. .
> FRIAM Applied Complexity Group listserv
> Fridays 9a-12p Friday St. Johns Cafe   /   Thursdays 9a-12p Zoom
> https://bit.ly/virtualfriam
> to (un)subscribe http://redfish.com/mailman/listinfo/friam_redfish.com
> FRIAM-COMIC http://friam-comic.blogspot.com/
> archives:  5/2017 thru present
> https://redfish.com/pipermail/friam_redfish.com/
>   1/2003 thru 6/2021  http://friam.383.s1.nabble.com/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://redfish.com/pipermail/friam_redfish.com/attachments/20221126/9a8b011a/attachment.html>