Print

---

Assuming you want to stay in Excel, here's VBA code which I think will do
what you want:

Public Function LargestValueInString(str As String, min As Integer, max As
Integer) As Integer
Dim match As Variant
Dim val As Integer, hival As Integer
hival = -1

   With CreateObject("VBScript.RegExp")

      .Pattern = "[0-9]+"
      .IgnoreCase = True
      .Global = True

      For Each match In .Execute(str)
         If Len(match.Value) < 5 Then  ' if you want bigger values you'll
need doubles not ints
            val = CInt(match.Value)
            If (val >= min) And (val <= max) And (val > hival) Then hival =
val
            End If
      Next
   End With

LargestValueInString = hival
End Function

I've attached a spreadsheet with the code and an example.  If it doesn't
make it through to you, let me know and I'll send it offline.  And it's in
Excel 2010, which is what I happen to have, but I guess I can save it in
another format if you want.

Graeme Williams
Waltham, MA


On Tue, Jul 2, 2013 at 1:05 PM, Brad Rhoads <[log in to unmask]> wrote:

> Hi Cindy,
>
> You can *almost* use text to column to split the fields out from the
> string. Then you take the max() of the resulting range. *Except* there's a
> max of 256 columns.
>
> So you'll need to split the string into parts, perhaps into separate
> worksheets. Then do the text to columns in worksheet and put the max of
> that work sheet at the end. Then in a final worksheet, take the max of each
> max column in each worksheet.
>
> Unfortunately, there is no text to rows function, but you can roll your own
> following this scheme(
>
> http://www.excelforum.com/excel-formulas-and-functions/401500-how-do-you-convert-text-to-rows.html
> ).
>
> Either scheme would work....
>
> Just had another idea: 1st open your data in Word, the convert the
> field delimiters to returns. Save it as plain text. Import it into Excel
> and you'll have all the data in one row. At the bottom of the row, you take
> the max of the row.
>
> God's Love,
>
> Brad
>
> ---------------------------
> www.maf.org/rhoads
> www.ontherhoads.org
>
>
> On Tue, Jul 2, 2013 at 9:02 AM, Harper, Cynthia <[log in to unmask]> wrote:
>
> > Is there a way to return (in Excel, if possible) the largest 4-digit
> > number (by word boundaries) in a string?  I've extracted the 863 fields
> > from Millennium for my active periodicals, and want to find the latest
> year
> > in each run.  I'm willing to estimate it by taking the largest 4-digit
> > number in the string. I'm doing this in Excel.  Any help?
> >
> > Cindy Harper
> > Electronic Services and Serials Librarian
> > Virginia Theological Seminary
> > 3737 Seminary Road
> > Alexandria VA 22304
> > 703-461-1794
> > [log in to unmask]
> >
>