LinkBack URL
About LinkBacks
Hello,
I am trying to use =PROPER to automatically capitalize first word in certain cells.
I inserted a cell next to column E and add this =PROPER(C2:E706)
It is not working. Can someone please help me fix this.
Thank you.
rmcc
Hello,
I am trying to use =PROPER to automatically capitalize first word in certain cells.
I inserted a cell next to column E and add this =PROPER(C2:E706)
It is not working. Can someone please help me fix this.
Thank you.
rmcc
You probably want
=PROPER(C2) in cell F2
then drag that over to H2, select F2:H2 and drag down to H706
Hi rmcc,
It is hard to understand what you want from your formula. Perhaps the example attached will help.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
I tried to replicate your example but I am not getting the correct result.
Here is what I did
proper.png
As you can see it is not working properly.
the provided solution to you needs other cells then copy paste those cell to the original..
you cannot change "within a cell" bu using formula maybe vba something like this..
right click on the tab - choose view code.on the window that will appear click insert - module
paste the sample code below in the window that will appear
select the columns that will be converted to proper the hit alt+F8 - to run the macro
Please Login or Register to view this content.
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Hi vlady,
I followed your instructions to the letter and got an error message "Compile error: Variable not defined" I know what I'm doing and why I get this error. I wonder if the OP can figure it out?
There are at least 2 problems with this question:
1. When the OP says "I am trying to use =PROPER to automatically capitalize first word in certain cells."
The Proper function will capitalize the first letter of every word in the cell specified. They say they want the "FIRST WORD"
2. When they wrote "I inserted a cell next to column E and add this =PROPER(C2:E706) "
The Proper function works on "text" not on a range of cells.
When you answer this with the phrase (that should start with a capital letter and be a complete sentence ) of:
I have no idea what you want the OP or me to do. It is like we speak different languages.the provided solution to you needs other cells then copy paste those cell to the original..
If the goal is to give the OP a solution or education enough to figure the problem out for themselves, then we should try to help in this goal.
This is why I try to give example files attached to my posts so people can experiment with my formulas or code and hopefully learn from them.
I was disappointed that you didn't supply an attachment with your code so I could get my error message from your original code.
I hope the OP finds an answer to the problem. I believe it lies in understanding that PROPER() will make EVERY first letter of words in a cell UPPER CASE. I hope they also see that sending a range of cells to the PROPER() function is not allowed and returns #Value.
Last edited by MarvinP; 07-22-2012 at 11:17 PM.
Good am Marvin... i'm not not good at vba but i think the code runs. here's the sample file...
i provided this because i took a look at the given "picture " of the OP... the arrow pertaining to "not" being capitalized is the target cell for the function proper given so i think the op wants to capitalized the words in that same cell better look at his picture.(if i'm not mistaken) he is expecting that the target cell will change even though the function proper was used in different cell.
Proper Case vba.xlsm
btw thanks for pointing out the importance of a sample file. thanks appreciate it.
Last edited by vlady; 07-22-2012 at 11:46 PM.
Hello,
Thank you for your reply but I didn't get the correct result.
I wanted to format certain cells so that when information is added in the future, excel will automatically convert text to proper.
Originally Posted by vlady
the provided solution to you needs other cells then copy paste those cell to the original..
you cannot change "within a cell" bu using formula maybe vba something like this..
right click on the tab - choose view code.on the window that will appear click insert - module
paste the sample code below in the window that will appear
select the columns that will be converted to proper the hit alt+F8 - to run the macroPlease Login or Register to view this content.
---------- Post added at 09:20 AM ---------- Previous post was at 08:37 AM ----------
Hi MarvinP,
Yes it did return #Value.
Is there a way to format a range of cells so that when text is entered it will automatically convert text to PROPER?
Yesterday icestationzbra show me how to format a range of cells so that when text is enter Excel automatically add other data to the entered text; this works great, so there must be a way to format a range of cells so that Excel will automatically convert the text to PROPER
Thank you very much for any help you can provide.
rmcc
---------- Post added at 09:28 AM ---------- Previous post was at 09:20 AM ----------
Hi vlady,
Thank you for your reply.
I opened the file and followed the instructions and it did change the range of cells to PROPER, but is there a way to format a range of empty cells so that Excel will convert any text to PROPER.
Thank you kindly for any help you can provide.
rmcc
---------- Post added at 09:46 AM ---------- Previous post was at 09:28 AM ----------
Hi Cutter,
I am fairly new to forums and I did find the SOLVED button.
I am still working on trying to format a range of empty cells so that Excel with automatically convert the text to PROPER.
I wasn`t able to make your earlier suggestion work. Is there any other help you can provide?
Thank you Cutter.
rmcc
@Vlady,
The reason your code doesn't run is a setting in VBA to have "Option Explicit". You should make sure you have this set as a default on ALL your VBA code.
read http://www.cpearson.com/excel/declaringvariables.aspx
I got the error with your code above because you didn't define your variables before using them.
Also - do you know the double negative rule? When you wrote above that.
Does this mean you ARE good at vba?i'm not not good at vba but i think the code runs.
The reason we give answers is so we can solve peoples problems not confuse them more.
Please be more maticulous in giving answers and suggestions that are correct.
BTW - thanks for attaching a sample file as I've found this normally helps in giving a correct solution.
Last edited by MarvinP; 07-23-2012 at 10:35 AM.
@vlady, i hope you don't mind that i have reused your file to update code in it that will automatically convert from any_case to proper_case.
@rmcc, see if this works for you. enable macros on this file > type any word in aNY_CASe in column A and hit enter > see it automatically change to Proper_case.
Last edited by icestationzbra; 07-23-2012 at 11:16 AM. Reason: updated with correct version
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
rmcc, can you please set this thread to SOLVED, too, since you have the resolution through another thread - this one?
Last edited by icestationzbra; 07-24-2012 at 05:13 PM.
@Ice
Based on your last post I'll mark this thread as solved.
Thanks.
Thank you Cutter for adding SOLVED to this thread; I did add SOLVED to the other threads and missed this one.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks