I remember building my own functions way back in Excel 4, but it's been so
long since I've delved that deeply into Excel that my more advanced skills
have atrophied almost to the vanishing point. Too bad, because I really did
use to be a wizard at this stuff. I'm now starting an effort to get back to
that point.

Thanks for reminding me that custom functions can be built. I have worked
with VBA in the past (another one of those atrophying skills) so hopefully
it won't take too long for that knowledge to come back.

--Tom

"macropod" wrote in message
...
Hi Thomas,

You could build your own function to do this. For example:
Function FindDigit(Str As String) As Integer
Dim Pos As Integer, i As Integer
For i = 0 To 9
If InStr(Str, i) 0 Then
If Pos 0 Then
Pos = Application.WorksheetFunction.Min(Pos, InStr(Str, i))
Else
Pos = InStr(Str, i)
End If
End If
Next
FindDigit = Pos
End Function

If you place the above function into a standard vba module for the
workbook, you can use a formula like:
=FindDigit(A1)
to retrieve the first digit's position, or '0' if there are no digits in
the string.

--
Cheers
macropod
[Microsoft MVP - Word]


"Thomas M." wrote in message
...
That works great. I'm a little surprised that there doesn't appear to be
a built-in function for this. I was expecting, for example, something
like a variation on the FIND or SEARCH functions that would essentially
direct the function to return the position of the first digit in a string
value.

In my particular situation the text string will always contain a number,
but writing formulas to cover as many situations as is reasonably
possible is always the best approach.

Thanks for the help.

--Tom

"Ken Wright" wrote in message
...
Does assume though that there actually is a number in there somewhere.
If there isn't then an erroneous answer will be returned. Likewise if
the cell were blank, a 1 will be returned.

=IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345678 9"))LEN(A1),0,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1 &"0123456789")))

Will cover all eventualities I think.

Regards
Ken.........................


"macropod" wrote in message
...
Hi Thomas,

Try:
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") )
where the string you're testing is in A1.

--
Cheers
macropod
[Microsoft MVP - Word]


"Thomas M." wrote in message
...
Excel 2007

Is there a way to find the position of the first digit within a text
string?

--Tom