EXCELL - trying to change all caps to first letter upper and lower

Hi jameshughbanks,

You can use this formula for A1

=LEFT(A1,1) & RIGHT(A1,LEN(A1)-1)

Sorry correction:

=UPPER(LEFT(A1,1)) & LOWER(RIGHT(A1,LEN(A1)-1))

or use a function like this (for the selected range)

Sub ChangeCase()
Dim rng As Range
Dim cell As Range
Set rng = Selection 
For Each cell In rng
    cell.Value = UCase(Left(cell.Value, 1)) & LCase(Right(cell.Value, Len(cell.Value) - 1))
Next 
End Sub

Select allOpen in new window

Hi James,

A circular reference is build up...when you use formulas linked to each other in calulating results..for instance...if you write in A1 that is equal to B1 and similarly if you write in B1 that is equal to A1...it will give you a circular reference error because both are interlinked to each other and excel doesnt calculate interdependency like this...

To trace out circular reference...use these steps...

If the Circular Reference toolbar is not displayed, click Customize on the Tools menu, click the Toolbars tab, and then select the Circular Reference check box.
On the Circular Reference toolbar, click the first cell in the Navigate Circular Reference box.
Review the formula in the cell. If you cannot determine whether the cell is the cause of the circular reference, click the next cell in the Navigate Circular Reference box.
Note   The status bar displays the word "Circular," followed by a reference to one of the cells contained in the circular reference. If the word "Circular" appears without a cell reference, the active worksheet does not contain the circular reference.

Continue to review and correct the circular reference until the status bar no longer displays the word "Circular."

Also if you just want to turn the first letter into capital..you can use...

=Proper(A1)

This will only turn the first letter into caps and rest into small...

HTH...

Saurabh...

=Proper() or VBA StrConv(,vbProperCase) turns the string to Proper Case that means first letter of each work in upper case and the rest in lower case. Like:

"this an example" into "This An Example"

I understoud that he wants only the first char of the string into upper case like:

"this an example" into "This an example"

Plese give me directions using word 2007.  i try and use the simple......= proper() --(turns the value to numerical 0 instead of word with upper and lower-------------- or the VBA STR...(this turns it to a some type of lettering or language I cannot read

>> Plese give me directions using word 2007
But you asked for Excel not Word ! What are you trying to do ?

sorry --excell 2007

Check some examples:
examples.xls

i tried exact formulas and did not compute.  attaching actual file to see..

after looking at the file, please layout my process to complete the other 10 excel files i have like that one

Where is the file ? Can you use Excel 2003 format (use the save as) ?

why does the extention of an excel file not allowed to load?  It ewill not attache

If you save as Excel 2003 format (*.xls) it allow.

i saved as compatible with it but does not upload..any way for direct exchange of file?

Strange ...

You can try zip it or use www.ee-stuff.com

i tried to rename the file... that worked.  I had to have it as a csv file to import into my outlook.  now you can help me..thank you so much for  your patience

james
financial-planners-5-sw-florida-.xls

Ok, I have the file! What column(s) would you like to change ?

Try this:

(select the range you want and then press the button)
financial-planners-5-sw-florida-.xls

i need to send out letters and merge files. So the whole spreadsheet (except the ST) state--- needs to be first letter caps and the rest lower case for each word.  Please explain the process so I can do it to all the other spreadsheets that are the same
thanks

Have you tried the file I have upload it ?

I have nopt done that from this site,,,,where do I go to download the file?

Look the ID:22054199

I have upload an example for you.

where do i go on this site with the id to find the file?

This one:
financial-planners-5-sw-florida-.xls

i found it - how do i select the ranges to apply the button to.  I tried to highlight and hit the buttom but a macro error came up

What error do you have ?

You have to select, using the mouse cursor, any area of the worksheet. Then press the button.

Make shure that the macros security (menu tools) is set to low.

2007 is different location than 2003 excell items...i did find the macros and tried to enable all of them but it did not work.  the error message reads """cannot run macro financilial....  the macro may not be available in this workbook or all macros may be disabled

>> Make shure that the macros security (menu tools) is set to low.

Yes, menus items have different location BUT the error is the same. You have to look for macros and change the security to low.

I'm sorry but I don't have Excel 2007 to test it :(

finally got it to work.  i had to close it and reopen to take effect.  thankssss...now how do i do all the other spreadsheets

If you need in every workbook you have to create an Excel Add-Ins

Have a look on this example:
http://office.microsoft.com/en-us/excel/HA102189961033.aspx

(I never done that in 2007 so I have showed you this example)

can't i just copy what you placed on that file and paste it to the new one (which is the same, just differernt data

Yes, but I understoud that you have several files that you want to use.

That way you can simple add a new button and paste the code on the VBA Editor (press ALT+F11 to open it)

i just copied and pasted to other files and it worksd great...thank you so much for your time and patience

Glad I could help!

unbelievable patience on part of the expert...what a great job!