Thread: Finding value in text string

1. Finding value in text string

Hi All

This is a bit difficult to explain.

I have been provided with a spreadsheet template that I can't change.

In the attached example in the template worksheet you can see some lines of text in cell I4.

In the sign summary worksheet you can see that I have set up a formula that counts the occurrence of the words "No unauthorised Entry" this works fine, however (and here is the tricky part - not even sure if this is possible), but I need to capture that I need 4 of them as shown in the Sign Summary worksheet J4.

Have any of the experts out there got any ideas on how this could be achieved (if at all?)

I hope that makes sense.

Any suggestions/solutions would be much appreciated

Regards

..just change the formula to:
Code:
`=IF(\$B3="","",IF(J2="","",COUNTIFS(Template!\$B\$4:\$B\$100,\$B\$3,Template!\$I\$4:\$I\$100,"*"&J2&"*")))*4`
zeddy

3. Hi Zeddy,

Thanks for the suggestion, but sort of defeats the purpose of using a formula in the first place, it seems to me that I might as well just put in the number 4 in the cell.

In the real spreadsheet there will be numerous (50 -60) entries in the template and the intention of using a formula is to prevent doubling up on on data to reduce the chance of errors between the "Template" and "Sign Summary". Ideally the formula in the "Sign Summary" would pick up the data (based on the countifs) but put 4 (in the example) into the respective cell.

I'm not even sure if what i'm trying to do is even possible, but I hope that this all make sense.

Regards

Here is one possible solution using a UDF. The user defined function will count the occurrences and the captured need amount on one line.

Place the following code in a standard module:
Code:
```Public Function COUNTOCCUR(rng As Range) As String
Application.Volatile
Dim cell As Range
Dim col As Integer, count As Integer
Dim item As String
col = Application.Caller.Column
item = Application.Caller.Offset(0, -8).Value
strng = Cells(2, col)
count = 0
For Each cell In rng
If cell.Offset(0, -7) = item And InStr(1, cell, strng, vbTextCompare) > 0 Then
count = count + 1
COUNTOCCUR = count & Right(cell, 4)
End If
Next cell
End Function```
In cell J3, enter the following formula: =COUNTOCCUR(Template!I3:I30) and fill in the Item in B3. This will count all occurrences of "No Unauthorised Entry" on the Template sheet and indicate the number needed

This could also be done like you had set up pulling over a total row by row using a formula that I will post shortly

HTH,
Maud

5. Hi Maud

..but what if one of them was
• Install 'No Unauthorised Entry' signage (x7)

zeddy

6. ..I too assumed that you always needed 4 of the signs for each match, so that's why I just used the *4

zeddy

7. Hey zeddy,

I just assumed that whatever the number that was for one with a certain paired Item (ex. J1) and text (ex. 'No Unauthorised Entry') would be the same for all of the matching pairs. But the code takes the number from the last match.

8. ..we must await enlightenment from verada then

zeddy

9. Hi Maud, zeddy,

Thanks so much for your assistance so far.

To help clarify, the number following the text (ex. 'No Unauthorised Entry') would more than likely change for each location.

ie J1 could be 'No Unauthorised Entry' (2), J2 could be 'Confined Space' (7) & 'No Unauthorised Entry' (1), etc, etc, the number will depend on the requirement for the particular location.

I hope that helps

Regards

I modified my code to take care of those cells that have multiple lines separated with Alt-<Enter>. The code is now generic in that you can place the same exact formula with the same range in every cell (use absolute referencing).

On the Sign Summary sheet cell C3, enter the formula =COUNTOCCUR(Template!\$I\$4:\$I\$31) and copy to L49.
Returned results are generated in the format count(xneededcount). ex: 2(x3) in which the item/description pair displayed two times with a needed count of three.

HTH,
Maud

In a standard module:
Code:
```Public Function COUNTOCCUR(rng As Range) As String
Application.Volatile
'-------------------------------
'DECLARE AND SET VARIABLES
Dim cell As Range
Dim col As Integer, row As Integer, count As Integer
Dim item As String
col = Application.Caller.Column
row = Application.Caller.row
item = UCase(Cells(row, 2))
'item = Application.Caller.Offset(0, -8).Value
strng = Cells(2, col)
count = 0
'-------------------------------
'CYCLE THRU RECOMMENDED ACTIONS
For Each cell In rng
With Worksheets("Template")
If UCase(.Cells(cell.row, 2)) = item And InStr(1, cell, strng, vbTextCompare) > 0 Then
'-------------------------------
'LOOK AT EACH LINE OF MULTILINE VALUE
s = Split(.Cells(cell.row, 9), Chr(10))
For I = 0 To UBound(s)
If InStr(1, s(I), strng, vbTextCompare) > 0 Then
count = count + 1
COUNTOCCUR = count & Right(s(I), 4)
End If
Next I
End If
End With
Next cell
'-------------------------------
'ZERO VALUE IF NO MATCH
If count = 0 Then COUNTOCCUR = 0
End Function```
Template Sheet:

Sign Summary Sheet:

11. Maud - you are amazing!!

Is it possible to modify the code to return just the number, ie instead of 1(x4) it just returns the value 4

Thanks so much

Regards

Replace this line of code:
Code:
`                COUNTOCCUR = count & Right(s(I), 4)`
with these lines of code:
Code:
```                COUNTOCCUR = Right(s(I), 4)
COUNTOCCUR = Replace(COUNTOCCUR, "(", "", , , vbTextCompare)
COUNTOCCUR = Replace(COUNTOCCUR, ")", "", , , vbTextCompare)
COUNTOCCUR = Replace(COUNTOCCUR, "x", "", , , vbTextCompare)```

13. Hi Maud

..will this work if there were (x10) or, heaven forbid, (x100) signs required?

Great job with the custom function!

zeddy

14. Yes, because the code is looking for the last 4 characters. So the last 4 of (x100) would still grab x100) then the code removes the "x" and the ")". The only thing that would mess it up would be trailing spaces that could be cured with a Trim function.

15. Trim?

Hi Maud

This is looking very close.

Could you please have a look at the attached (Sign Summary) - I guess that this would be where the trim function would be needed as you suggested - would you be able to assist (once again!) with additional code (or formula) to resolve this result.

In the Sign Summary, the =sum(...:...) does not return a value just "0" and is it possible to hide 0 values in the cells. The way i normally do it (conditional formatting or hide zero values don't seem to work.

Much appreciated

Regards

Page 1 of 2 12 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•