Changing Case in a Portion of a Cell

[Bob H]

Is there a way to highlight a portion of a phrase in an Excel cell and
convert the phrase to capital letters (lower case to upper case), rather
than retype the entire phrase? Also is it possible to highlight a portion
phrase and have all the first letters of words convert from lower case to
upper case? I have to go through an Excel document and convert sections of
phrases to all upper case letters, I would rather not retype the entire
section. I do not believe a formula will work as a portion of the cell must
be all caps and a portion lower case or proper, and because the data has
already been inputted.

Thanks, Bob

 

[Don Guillett]

Characters Property
See Also Applies To Example Specifics
Returns a Characters object that represents a range of characters within the
object text. You can use the Characters object to format characters within a
text string.

expression.Characters(Start, Length)

expression Required. An expression that returns an object in the Applies
To list.

Start Optional Variant. The first character to be returned. If this
argument is either 1 or omitted, this property returns a range of characters
starting with the first character.

Length Optional Variant. The number of characters to be returned. If this
argument is omitted, this property returns the remainder of the string
(everything after the Start character).

Remarks
The Characters object isn't a collection.

For the TextFrame object, Characters is a method.

Example
This example formats the third character in cell A1 on Sheet1 as bold.

With Worksheets("Sheet1").Range("A1")
.Value = "abcdefg"
.Characters(3, 1).Font.Bold = True
End With

 

[Guest]

A formula can change it from LOWER to UPPER at some point throughout, but I
can't get it to change back to LOWER for the last part........

=LEFT(A1,3)&UPPER(MID(A1,4,3)&LOWER(MID(A1,7,5)))
abcdefghijk results in abcDEFGHIJK

You could separate the parts into three columns, maybe using TextToColumns,
and then do the formatting and then CONCATENATE them back together........

hth
Vaya con Dios,
Chuck, CABGx3

 

[Bob H]

Ok, excuse my ignorance but maybe a reply could be dummied up a bit. The
phrases in the cells are all of various lengths. I know with Microsoft Word
I highlight the string then "Format - Change Case". Is there anything
similar in Excel? What I have to do is re-format a list of businesses and
property references. What I have now in each cell is something like this:

jane's retail clothing ltd pr: jane smith

What I have to turn it into is:

JANE'S RETAIL CLOTHING LTD. pr: Jane Smith

In total, I have roughly 1000 entries per volume (and there are 5 volumes
left) to go through. It would be much easier for me if I could just
highlight "jane's retail clothing ltd" and turn it into JANE'S RETAIL
CLOTHING LTD

thanks, Bob

 

[Don Guillett]

see if this does what you want
Sub uppercompanyname()
For Each c In Range("f2:f5")
c.Value = UCase(Left(c, InStr(c, ":")))
Next c
End Sub

 

[Don Guillett]

Actually, the 1st one only gives the company and this ucases the company and
gives you the rest.

Sub uppersome()
For Each c In Range("f2:f5")
'c.Value = UCase(Left(c, InStr(c, ":")))
x = InStr(c, ":")
c.Value = UCase(Left(c, InStr(c, ":"))) & Right(c, Len(c) - x)
Next c
End Sub