need VBA to Cap first letter in each cell in one column

[skyport]

As always I return to the great resource of Mr. Excel on this one.

I have column G on sheet 2 which will have data inputted. The first letter in each cell in column G needs to be upper case but done in a manner that does not eliminate the existing caps to other words in the sentence within the cells.

EXAMPLE: my impression is that Mary is the right choice for Montana.

The result should cap the word "my" but still also leave Mary and Montana in upper case.

The reason this needs to be a VBA and not a formula is because the cells in column G already contain a formula for another purpose.

Can anyone help with this one pleae?

 

[James006]

Hi,

Why not the following formula ...

=UPPER(LEFT(G1,1))&RIGHT(G1,LEN(G1)-1)

HTH

 

[skyport]

Hi James, thanks for helping me. As I mentioned, it cannot be a formula for the cells because there already exists one that performs another function. Therefore it would need to be a VBA.

 

[James006]

If in cell G1 you currently have a formula ...just take your whole formula and copy it to replace G1 in the above formula ...

 

[Trebor76]

Deleted - my bad.

 

[hiker95]

As always I return to the great resource of Mr. Excel on this one.

I have column G on sheet 2 which will have data inputted. The first letter in each cell in column G needs to be upper case but done in a manner that does not eliminate the existing caps to other words in the sentence within the cells.

EXAMPLE: my impression is that Mary is the right choice for Montana.

The result should cap the word "my" but still also leave Mary and Montana in upper case.

The reason this needs to be a VBA and not a formula is because the cells in column G already contain a formula for another purpose.

Can anyone help with this one pleae?

skyport,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

Screenshots will not work because they will not display formulae.

The following is a free site:

You can upload your workbook to (the BLUE link-->) Box Net ,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

 

[hiker95]

Screenshots will not work because they will not display formulae.

skyport,

My bad, the following two can display formulae:


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

1. Click on the below link to see How to display your sheet, and, how to install, download, and, use the MrExcel HTML Maker:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html

2. Excel Jeanie
Download

 

[skyport]

Hi Hiker 95. Using PC, Windows 7 and Excel 2000. The formula that occupies the cells are only needed there until data is entered and then the formula is replaced by the data itself as each cell is filled with the data. At the end of all the cells being filled with data in column G, is when I would need to run a VBA to cap the first letter in each cell that has the data

 

[hiker95]

Hi Hiker 95. Using PC, Windows 7 and Excel 2000. The formula that occupies the cells are only needed there until data is entered and then the formula is replaced by the data itself as each cell is filled with the data. At the end of all the cells being filled with data in column G, is when I would need to run a VBA to cap the first letter in each cell that has the data

skyport,

Here is a macro solution for you to consider, based on variations of your sample string.

Sample raw data:

Excel 2007
	G
1	my impression is that Mary is the right choice for Montana.
2	your impression is that Mary is the right choice for Montana.
3	mary is the right choice for Montana.
4	the right choice for Montana is Mary
5
Sheet2

After the macro:

Excel 2007
	G
1	My impression is that Mary is the right choice for Montana.
2	Your impression is that Mary is the right choice for Montana.
3	Mary is the right choice for Montana.
4	The right choice for Montana is Mary
5
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub CapFirstLetter()
' hiker95, 09/09/2015, ME881582
Dim c As Range, t As String
Application.ScreenUpdating = False
With Sheets("Sheet2")
  For Each c In .Range("G1", .Range("G" & Rows.Count).End(xlUp))
    If Left(c, 1) Like "[a-z]" Then
      t = UCase(Left(c, 1))
      c = t & Mid(c, 2, Len(c))
    End If
  Next c
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the CapFirstLetter macro.

 

[skyport]

Hiker 95, thanks again, the code worked perfect. Always good to see you again with great solutions.

James006, I tried the formula route but couldn't seem to make that work without disrupting the original formula and its' function