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

trying to change a data sheet in excell with all caps to first letter upper and the rest lower case for a group of columns.  i try to put in my formula and it appears it will do fine but a "circular reference" error come up and when I say O.K. a numerical value of 0 goes in the cell and replaces the name.  I cannot find the circular referenrce to turn it off..need help

Last Comment
jameshughbanks
Jorge Paulino

Hi jameshughbanks,

You can use this formula for A1

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

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
``````
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...
Jorge Paulino

=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
Jorge Paulino

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

sorry --excell 2007
Jorge Paulino

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

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

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?
Jorge Paulino

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

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

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

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?
Jorge Paulino

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?

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

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

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

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

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

Jorge Paulino

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY