Changing Case in a Portion of a Cell

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

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
--
Don Guillett
SalesAid Software
[email protected]
"Bob H" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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" wrote:

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Bob H" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

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
SalesAid Software
[email protected]
"Bob H" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "Bob H" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>>
>>
>>
>
>

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

--
Don Guillett
SalesAid Software
[email protected]
"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> 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
> SalesAid Software
> [email protected]
> "Bob H" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>> --
>>> Don Guillett
>>> SalesAid Software
>>> [email protected]
>>> "Bob H" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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
>>>>
>>>>
>>>
>>>
>>
>>
>
>