Find the First Digit in a Text String
 

Excel 2007

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

--Tom

Find the First Digit in a Text String
 

Excel experts may have more elegant suggestions for you.
Anyway, you could try the following formula (which assumes that the string
is in A1. Change it appropriately.

=FIND(1,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUT E(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBS TITUTE(A1,2,1),3,1),4,1),5,1),6,1),7,1),8,1),9,1), 0,1))

Best regards,
B. R. Ramachandran



"Thomas M." wrote:

Excel 2007

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

--Tom

Find the First Digit in a Text String
 

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

Find the First Digit in a Text String
 

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

Find the First Digit in a Text String
 

Assuming he is using 2007 and not a previous version that would return an
error when he tried to enter a formula with more than 7 nested functions of
course. :-)

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


"B. R.Ramachandran" wrote in
message ...
Excel experts may have more elegant suggestions for you.
Anyway, you could try the following formula (which assumes that the string
is in A1. Change it appropriately.

=FIND(1,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUT E(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBS TITUTE(A1,2,1),3,1),4,1),5,1),6,1),7,1),8,1),9,1), 0,1))

Best regards,
B. R. Ramachandran



"Thomas M." wrote:

Excel 2007

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

--Tom

Find the First Digit in a Text String
 

Hi Ken,

Does assume though that there actually is a number in there somewhere.

Yep - I didn't bother with error checking, because it wasn't asked for. I do agree, though, that it's generally a good idea to do so
and your approach does indeed seem to have all possibilities covered.

--
Cheers
macropod
[Microsoft MVP - Word]


"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

Find the First Digit in a Text String
 

:-)

Hi Stranger - hope all is well with you - Seems like an absolute age since I
was posting on here regularly.
Nostalgia bug biting now, so heading off to Tek-Tips :-)


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


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

Does assume though that there actually is a number in there somewhere.

Yep - I didn't bother with error checking, because it wasn't asked for. I
do agree, though, that it's generally a good idea to do so and your
approach does indeed seem to have all possibilities covered.

--
Cheers
macropod
[Microsoft MVP - Word]


"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

Find the First Digit in a Text String
 

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

Find the First Digit in a Text String
 

You're very welcome :-)

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

"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

Find the First Digit in a Text String
 

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

Find the First Digit in a Text String
 

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