Excel Formatting

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

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

On Sep 20, 11:48 am, 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

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

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

Gary''s Student wrote:
so you want to change handy into handY ??

So, to rectify that problem, maybe you need to find "and " (and, space)

Beege

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