Excel Formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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!
Click to expand...

You could first determine whether the cell contains the word "and" by
using the Find function.
Eg. Upper and Lower - your syntax would be =find("and",cell). This
will tell you where "and" starts. In this case-7. So your entire
dataset should have numbers and if it does not, it should return a
weird value or 0. The next step would then be to Captalize it using
the Upper function. So you could write an If statement= If(cell value
0, upper(the original cell value) else return orginal value) and it
Click to expand...
will capitalize the word in that cell or leave it the way it was
This may be a slightly long way of doing it but it should work
 
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 !
 
Back
Top