how to search for numbers in a string

Microsoft Excel 2007
February 18, 2010 at 13:10:10
Specs: Windows XP
I have a problem with searching in a string. I want to find a number like 1 but when I use :

If InStr(tasks(d), CStr(coursegoal)) Then

the cells contain data like:
cell1 1,3,4,12
cell2 3,11,13
cell5 1,10

It says that 10,11,12,13 also is number 1 but it's not. So my question is there a way that I can just check for a special number like 1. Please help me.


See More: how to search for numbers in a string

Report •

#1
February 18, 2010 at 15:12:13
Hi,

Here is a custom function that will return the position of numbers such as 1 or 11 in a list of numbers separated by commas in a single cell.

	A	B		C
6	1		
7		1,2,3,33,12	1
8		2,3,33,12	0

Cells B7 and B8 contain the text to be searched
Cell A6 contains the 'number' to be found, 1 in this example.
Cells C7 and C8 contain the result - C7 position 1, but C8 returns zero as '1' was not found.

The formulas are
C7 =findNum(B7,$A$6)
C8 =findNum(B8,$A$6)

Here is the code for the function which was placed in a standard module in the VB window

Public Function findNum(InText As Range, FindText As Range) As Variant
Dim strIn As String
Dim strFind As String
Dim strSubString As String
Dim strChar As String
Dim n As Integer
Dim intLocn As Integer

'get text to search
strIn = InText.Text
'get character to find
strFind = FindText.Text

'set substring to empty and first location to 1
strSubString = ""
intLocn = 1
For n = 1 To Len(strIn)
    strChar = Mid(strIn, n, 1)
    If strChar = "," Then
        'complete substring found, so test it
        If strSubString = strFind Then
            'it's a match so return location
            findNum = intLocn
            Exit Function
        End If
        'no match so reset substring
        strSubString = ""
        'set find location to position after comma
        intLocn = n + 1
        Else
        strSubString = strSubString & strChar
    End If
Next n

'if we get here test remaining sub string
If strSubString = strFind Then
    'it's a match so return location
    findNum = intLocn
    Else
    'no match - return zero
    findNum = 0
End If
End Function

You could also adapt this as a function to be called from another macro, replacing the ranges by actual strings passed to the function:

Public Function findNum(strIn As String, strFind as string) As Variant
Dim strSubString As String
Dim strChar As String
Dim n As Integer
Dim intLocn As Integer

'set substring to empty and first location to 1
strSubString = "" ... etc.

Regards


Report •

#2
February 21, 2010 at 05:18:33
Humar.
Thank you for your reply, unfortunately I couldn’t get it to work. I'm a beginner in programming with macro, and have a hard time with the function bit. But the cod you sent helped me to think outside the box and I found another solution. I'm going to post it here if anybody wants to se another way to solve the problem I had. But I warn you that it's not the best way or the prettiest :D

For i = 1 To 30
tasks(i) = Split(Cells(20, i + 4).Value, ",")
Next i

For t = 5 To 34
d = d + 1
For l = 0 To UBound(tasks(d))
If tasks(d)(l) = coursegoal Then 'InStr(tasks(d)(l), coursegoal) Then
myNextRow = myNextRow + 1
courseCounter = courseCounter + 1
Cells(myNextRow, 3) = HoldTask(d)
For Z = 1 To 29
Cells(myNextRow, Z + 3) = grades(a + 1)(Z, 1)
Next Z


End If
Next l

Next t

I split the cells text and put it in an array and then I loop through the array and check for the value.

Thanks again for the quick response and sorry for my bad English.
Marcus


Report •

#3
February 21, 2010 at 06:32:31
Hi,

As long as you have a working solutions that's OK

The code I posted was a user defined function, so you use it like any other function in a cell.

Note that the code starts with Public Function instead of Sub. This makes the code a function that can be used in an Excel cell. The function returns a number much the same as standard functions do. =Len(A2) will return a number for the length of the text in cell A2.

=findNum(A2,A3) will return the position of the text in cell A3 in the text in cell A2.

The code has to go into a Standard Module. Sub routine code (macros) often go in the worksheet object, but in the VBA window in the explorer pane on the left, right click your workbook name and Insert - Module (not Class module). Enter the function in the resulting Module that is added e.g., Module1.

Perhaps when you have time you can give it a try. The advantage of the UDF is that it runs automatically - you don't have to call a macro to get the result.

Regards


Report •

Related Solutions

#4
February 21, 2010 at 08:03:02
I will give it a try late on. It's much to learn. I'm working with C# and PHP for the moment. This is just a side project :D

Have a great one.
And thank you for the quick responses.


Report •

Ask Question