Re: Need formula to extract a numeric value from a free-format text

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I am still not sure if the OP wanted the 7-digit number to stand alone (as a "word") or not, so I just went for the first isolated 7 digits in a row whether imbedded in other text or not. The reason I thought that is because of the "dot" that followed the 7-digit number in the OP's posted example text. In thinking about it, I'm guessing you took that to be a period at the end of a sentence.

With that said, I made a mistake in my original function and left out the *MISSING* indicator. I just posted a corrected function against my original message for the function.

--
Rick (MVP - Excel)


--
Rick (MVP - Excel)


"Ron Rosenfeld" <ronrosenfeld@xxxxxxxxxx> wrote in message news:sqq365pt0f1apuv1lif9pcs620953m6kes@xxxxxxxxxx
On Sat, 18 Jul 2009 10:54:43 -0400, "Rick Rothstein"
<rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote:

Function First7DigitNumber(S As String) As String
Dim X As Long
For X = 1 To Len(S)
If Mid(" " & S & " ", X, 9) Like "[!0-9]#######[!0-9]" Then
First7DigitNumber = Mid(S, X, 7)
End If
Next
End Function

I note that given the following modification of the OP's test string:

"User requested authority of emergency ID for reason NHUSER1234567 Restore
of object to library LEVEL2 under remedy 61074317. 06/04/09 17:46 QPGMR"

your routine returns 1234567 whereas my UDF returns "MISSING" since there are
no seven digit words.

(Lori's formulas return 1074317)

--ron

.

Relevant Pages

  • Re: sum last rows
    ... Microsoft MVP Excel ... Rick ... Change my 'Data Sheet' to the name of your sheet with the columns you are adding ...
    (microsoft.public.excel.programming)
  • Re: Saving a file to desktop with VBA
    ... Rick (MVP - Excel) ... Windows, although I don't know that for sure as I do not do any ...
    (microsoft.public.excel.programming)
  • Re: Testing
    ... but I couldn't get a response through to your newsgroup thread. ... Rick (MVP - Excel) ...
    (microsoft.public.excel.misc)
  • Re: Assing a Name to a Range
    ... Rick (MVP - Excel) ... >> I need the named range for a chart, which I am building on the fly: ...
    (microsoft.public.excel.programming)
  • Re: Invoice Number Problem
    ... Rick (MVP - Excel) ... > Hi Mike ...
    (microsoft.public.excel.programming)