Clever formula required (to find the word directly preceding mychosen word)

[Gerry]

If my chosen word is "programme" and I want to find establish which word directly preceded "programme" in a cell (call it cell B23) (the cell may contain a long paragraph or two) can someone provide me with a formula for entry into cell C23 which will return the preceding word?

Can someone provide me with another formula which will consider that the paragraph in cell B23 may have 2 or more occurrences of the word "programme" and I would also like to know what the preceding word was in each of those occurrences.

For info - I have two thousand cells in column B!! Hoping to copy-down theformulae.

Thanks

Gerry.

 

[h2so4]

Gerry has brought this to us :

If my chosen word is "programme" and I want to find establish which word
directly preceded "programme" in a cell (call it cell B23) (the cell may
contain a long paragraph or two) can someone provide me with a formula for
entry into cell C23 which will return the preceding word?

Can someone provide me with another formula which will consider that the
paragraph in cell B23 may have 2 or more occurrences of the word "programme"
and I would also like to know what the preceding word was in each of those
occurrences.

For info - I have two thousand cells in column B!! Hoping to copy-down the
formulae.

Thanks

Gerry.

Hello,

I don't think you can solve it easily without an UDF.

Here is an example of an UDF that should do what you expect, If my
understanding is correct !

copy paste the following to a visual basic module
Alt-F11, Insert module, paste code

in cell C(23) write the formula =RWBP(B23)


Function RWBP(r As Range) As String
Dim t As String
Dim s As Integer
Dim j As Integer
Dim pw As String
Dim c As String

If r.Count <> 1 Then MsgBox "invalid function call"
t = r.Value

s = InStr(t, " programme")
While s <> 0
pw = ""
For j = s - 1 To 1 Step -1
c = Mid(t, j, 1)
If c <> " " And j > 0 Then
pw = c & pw
Else
Exit For
End If
Next j
RWBP = RWBP & pw & " "
t = Right(t, Len(t) - s - 8)
s = InStr(t, " programme")
Wend

End Function

 

[Ron Rosenfeld]

If my chosen word is "programme" and I want to find establish which word directly preceded "programme" in a cell (call it cell B23) (the cell may contain a long paragraph or two) can someone provide me with a formula for entry into cell C23 which will return the preceding word?

Can someone provide me with another formula which will consider that the paragraph in cell B23 may have 2 or more occurrences of the word "programme" and I would also like to know what the preceding word was in each of those occurrences.

For info - I have two thousand cells in column B!! Hoping to copy-down the formulae.

Thanks

Gerry.

If there were just one or two "programme"'s, it would be relatively simple to do it with formulas. Here's one way:

Word before first programme:
=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(B23,SEARCH(" programme",B23)))," ",REPT(" ",99)),99))

Word before second programme:
=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(B23,SEARCH(" programme",B23,1+SEARCH(" programme",B23))))," ",REPT(" ",99)),99))

But this will get unwieldy if there can be any number of "programme"'s

Easiest to do this with a User Defined Function

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

==FindWordBefore("programme",B23)

in some cell.

=================================
Option Explicit
Function FindWordBefore(WordToFind As String, StringToSearch As String) As String
Dim re As Object, mc As Object, m As Object
Dim s As String
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.Pattern = "\w+(?=\s+" & WordToFind & "\b)"
.ignorecase = True
End With

If re.test(StringToSearch) = True Then
Set mc = re.Execute(StringToSearch)
For Each m In mc
s = s & "; " & m
Next m
FindWordBefore = Mid(s, 3)
End If

End Function
===============================

 

[Gerry]

If my chosen word is "programme" and I want to find establish which word directly preceded "programme" in a cell (call it cell B23) (the cell may contain a long paragraph or two) can someone provide me with a formula for entry into cell C23 which will return the preceding word?



Can someone provide me with another formula which will consider that the paragraph in cell B23 may have 2 or more occurrences of the word "programme" and I would also like to know what the preceding word was in each of those occurrences.



For info - I have two thousand cells in column B!! Hoping to copy-down the formulae.



Thanks



Gerry.

THANKS VERY MUCH TO H2S04 AND TO RON. FANTASTICALLY CLEVER! (ESP. H2S04)

 

[h2so4]

Gerry was thinking very hard :

THANKS VERY MUCH TO H2S04 AND TO RON. FANTASTICALLY CLEVER! (ESP. H2S04

IMHO, Ron's solution is very clever. The way he masters regular
expressions puzzles me.

 

[Ron Rosenfeld]

IMHO, Ron's solution is very clever. The way he masters regular
expressions puzzles me.

Puzzle's? I'm no master, but I do enjoy using regular expressions, as in a number of scenarios, the programming is easier (for me). I just have to be careful of speed issues, on occasion.