T
Thomas M.
Excel 2007
Is there a way to find the position of the first digit within a text string?
--Tom
Is there a way to find the position of the first digit within a text string?
--Tom
B
B. R.Ramachandran
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(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,2,1),3,1),4,1),5,1),6,1),7,1),8,1),9,1),0,1))
Best regards,
B. R. Ramachandran
Anyway, you could try the following formula (which assumes that the string
is in A1. Change it appropriately.
=FIND(1,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,2,1),3,1),4,1),5,1),6,1),7,1),8,1),9,1),0,1))
Best regards,
B. R. Ramachandran
M
macropod
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.
Try:
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))
where the string you're testing is in A1.
K
Ken Wright
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&"0123456789"))>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.........................
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&"0123456789"))>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 said:
K
Ken Wright
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........................
error when he tried to enter a formula with more than 7 nested functions of
course.
Regards
Ken........................
M
macropod
Hi Ken,
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]
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 said:
K
Ken Wright
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 said:
T
Thomas M.
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
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 said:
K
Ken Wright
You're very welcome
Regards
Ken.....................
Regards
Ken.....................
Thomas M. said:
M
macropod
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]
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. said:
T
Thomas M.
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
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 said: