If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Excel Formatting
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 |
#3
|
|||
|
|||
Excel Formatting
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 |
#4
|
|||
|
|||
Excel Formatting
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 |
#5
|
|||
|
|||
Excel Formatting
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 |
#6
|
|||
|
|||
Excel Formatting
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 |
Thread Tools | |
Display Modes | |
|
|