I am going to apply ExcelHelpNeeded's thinking to VBA. Examine this UDF:

Function handy(r As Range) As String
v = r.Value
If Len(v) = Len(Replace(v, "and", "")) Then
handy = v
Exit Function
End If
s = Split(v, "and")
For i = 1 To UBound(s)
s(i) = UCase(Left(s(i), 1)) & Right(s(i), Len(s(i)) - 1)
Next
handy = Join(s, "and")
End Function

so if A1 contains:
dogsandcatsandpigsandgoats

then
=handy(A1) would return:
dogsandCatsandPigsandGoats

However, if A1 contains:
andandand
then you need a better coder than me !
--
Gary''s Student - gsnu200746


"dberger16" wrote:

yes, exactly, thank you in advance for the coding
--
DaveB


"Gary''s Student" wrote:

so you want to change handy into handY ??
--
Gary''s Student - gsnu200746


"dberger16" wrote:

In my spreadsheet, I would like to find all the "and" words and then
CAPITALIZE the very next letter or word in the same cell.

Thanks!
--
DaveB