Searching for a number in a string

Hi Perplexed

There are a few ways this can be done. If it is always 58 or 31 you are looking for then you can use this:

=IF(ISERR(MID(A1,FIND(31,A1),2)),VALUE(MID(A1,FIND(58,A1),2)),31)

If the number will not always be 31 or 58 let me know and I'll try whip up a Custom function.

Dave

OzGrid Business Applications

Dave,

Thanks for the feedback. Actually, the value that I will be searching for will be a variable. Any ideas???

Sure do! We could use a rather lengthy nested function, but you will probably find it easier and more reliable to use this User Defined Function I created.

Function ExtractNumber(rCell As Range)
Dim iCount As Integer, i As Integer
Dim sText As String
Dim lNum As Long
'Written by OzGrid Business Applications
'www.ozgrid.com
sText = rCell

For iCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, iCount, 1)) Then
i = i + 1
lNum = CInt(Mid(sText, iCount, 1)) & lNum
End If

If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))

Next iCount

ExtractNumber = lNum
End Function

To use it push Alt+F11 then go to Insert>Module and paste in the code. Then push Alt+Q to return to Excel and Save. Then in any cell use:

=ExtractNumber(A1)

Dave

OzGrid Business Applications

It seems to me that what you want is a logical test whether number X is embedded in string Y. There are two cases though. Consider your example set which I extended with a few items.

{"(MO)31->331*";"x58x";"a31";"a331";"b3131c"}

This set occupies the range: A1:A5.

Lets say that we put number X in B1. X can be any value. Lets X=31.

Case [a]

In C1 enter: =ISNUMBER(SEARCH($B$1,A1))+0

Copy down this formula as far as needed.

You get the following result set in C:

{1;0;1;1;1}, where 1 means TRUE, that is, X is a substring, even if it's part of a sequence of digits in a substring of the test string.

Case [b]

In D1 enter: =AND(ISNUMBER(SEARCH($B$1,A1)),NOT(ISNUMBER(MID(A1,SEARCH($B$1,A1)-1,1)+0)),NOT(ISNUMBER(MID(A1,SEARCH($B$1,A1)+LEN($B$1),1)+0)))+0

You get the following result set in D:

{1;0;1;0;0}, where 1 means TRUE, that is, X is a substring of the test string.

Aladin

=================== I am trying to search for numbers in cells with strings such as

To use it push Alt+F11 then go to Insert>Module and paste in the code. Then push Alt+Q to return to Excel and Save. Then in any cell use: =ExtractNumber(A1)

OK. That's a good start but the search will be done from a macro (VBA) that uses a range that
is based on two variables. Let me try to be clearer about what I am doing. The code will go
down a list of values on one worksheet. With each value, it will search another worksheet
for that value embedded in strings in each cell. If it finds the value in that cell then it
it writes the value of the column header to the first worksheet. If have the code all written
and working for everything but finding the value embedded in the string. Sorry if I wasn't
clear at first. Suggestions??