I have cells that look like so...

SS600S162-43
SS1100S162-43

I need to Pull the 600 or 1100 out of the string.

The string will always look like

Letter *** Letter *** 3-4 #'s *** Letter *** Letter *** ###-##

I need to pull the 3-4#'s out of the middle of it for use.

Thanks.

Here is a formula which evaluates Cell A1 and returns the middle 3 or 4
digits...

=IF(ISNUMBER(VALUE(MID(A1,6,1))), VALUE(MID(A1,3,4)),VALUE(MID(A1,3,3)))
--
HTH...

Jim Thomlinson


"Kevin O'Neill" wrote:

I have cells that look like so...

SS600S162-43
SS1100S162-43

I need to Pull the 600 or 1100 out of the string.

The string will always look like

Letter *** Letter *** 3-4 #'s *** Letter *** Letter *** ###-##

I need to pull the 3-4#'s out of the middle of it for use.

Thanks.

Wow very nice. Can that be translated into VBA? If not it is still OK,
I can probally work around it. Thanks.

NM, Jim, thanks, works very well.

Sorry. I got busy doing my job... :-)

Here is the code...

Sub test()
MsgBox FindDigits("SS600S162-43")
MsgBox FindDigits("SS1100S162-43")

End Sub

Public Function FindDigits(ByVal strInput As String) As Integer
If IsNumeric(Mid(strInput, 6, 1)) Then
FindDigits = CInt(Mid(strInput, 3, 4))
Else
FindDigits = CInt(Mid(strInput, 3, 3))
End If

End Function

--
HTH...

Jim Thomlinson


"Kevin O'Neill" wrote:

Wow very nice. Can that be translated into VBA? If not it is still OK,
I can probally work around it. Thanks.

On 5 Jan 2006 13:25:09 -0800, "Kevin O'Neill" wrote:

I have cells that look like so...

SS600S162-43
SS1100S162-43

I need to Pull the 600 or 1100 out of the string.

The string will always look like

Letter *** Letter *** 3-4 #'s *** Letter *** Letter *** ###-##

I need to pull the 3-4#'s out of the middle of it for use.

Thanks.

This should give you the technique:
====================
Sub prn()
Const str1 As String = "SS600S162 - 43"
Const str2 As String = "SS1100S162 - 43"

Debug.Print Val(Mid(str1, 3, 255))
Debug.Print Val(Mid(str2, 3, 255))

End Sub
===================
600
1100
===================

The '255' for the number of characters in the Mid function is just any number
that is large enough to include all the numbers. In this case, it could be as
small as '4'.

The method takes advantage of the fact that the Val function stops evaluating
the string once it gets to a non-digit value.


--ron

Ah interesting, I'll keep that in mind, thanks guys.