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

G

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.
 
H

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
 
G

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)
 
H

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top