convert a formula into a macro
 

For a variety of reasons I frequently have to convert long columns of text
into text for which the first three characters are in upper case and the rest
of the text string is in lower case.

Example: "Rastafarian" would have to be converted to "RAStafarian".

This is easy enough to do with a formula; i.e.,
=CONCATENATE(UPPER(LEFT(D14,3)),MID(D14,4,1000)) however I would like to
create a macro and attach it to a button so that I can convert these text
strings in one click.

How would I express the above formula in VBA syntax?

Thanks,

Dave
--
Brevity is the soul of wit.

convert a formula into a macro
 

Dave

Something like the below (Works on a selection)

Sub UPPERFirst3()
Dim myCell As Range
For Each myCell In Selection
myCell.Value = UCase(Left(myCell.Value, 3)) & Mid(myCell.Value, 4,
Len(myCell.Value) - 3)
Next myCell
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Dave F" wrote in message
...
For a variety of reasons I frequently have to convert long columns of text
into text for which the first three characters are in upper case and the
rest
of the text string is in lower case.

Example: "Rastafarian" would have to be converted to "RAStafarian".

This is easy enough to do with a formula; i.e.,
=CONCATENATE(UPPER(LEFT(D14,3)),MID(D14,4,1000)) however I would like to
create a macro and attach it to a button so that I can convert these text
strings in one click.

How would I express the above formula in VBA syntax?

Thanks,

Dave
--
Brevity is the soul of wit.

convert a formula into a macro
 

Wow, that's great.

Thanks.

Dave
--
Brevity is the soul of wit.


"Nick Hodge" wrote:

Dave

Something like the below (Works on a selection)

Sub UPPERFirst3()
Dim myCell As Range
For Each myCell In Selection
myCell.Value = UCase(Left(myCell.Value, 3)) & Mid(myCell.Value, 4,
Len(myCell.Value) - 3)
Next myCell
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Dave F" wrote in message
...
For a variety of reasons I frequently have to convert long columns of text
into text for which the first three characters are in upper case and the
rest
of the text string is in lower case.

Example: "Rastafarian" would have to be converted to "RAStafarian".

This is easy enough to do with a formula; i.e.,
=CONCATENATE(UPPER(LEFT(D14,3)),MID(D14,4,1000)) however I would like to
create a macro and attach it to a button so that I can convert these text
strings in one click.

How would I express the above formula in VBA syntax?

Thanks,

Dave
--
Brevity is the soul of wit.