Excel Formatting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dberger16 - 20 Sep 2007 18:48 GMT

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!

Signature

DaveB

Reply to this Message

Gary''s Student - 20 Sep 2007 19:08 GMT

so you want to change handy into handY ??

Signature

Gary''s Student - gsnu200746

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

Reply to this Message

dberger16 - 20 Sep 2007 19:14 GMT

yes, exactly, thank you in advance for the coding

Signature

DaveB

> so you want to change handy into handY ??
>
> > 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!

Reply to this Message

Gary''s Student - 21 Sep 2007 17:16 GMT

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 !

Signature

Gary''s Student - gsnu200746

> yes, exactly, thank you in advance for the coding
>
[quoted text clipped - 4 lines]
> > >
> > > Thanks!

Reply to this Message

Beege - 20 Sep 2007 19:25 GMT

> so you want to change handy into handY ??

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

Beege

Reply to this Message

ExcelHelpNeeded - 20 Sep 2007 19:09 GMT

> 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

Reply to this Message