LinkBack URL
About LinkBacks
Write a formula to capitalize the first letter of every word in Column A that have more than 100 characters.
Write a formula to capitalize the first letter of every word in Column A that have more than 100 characters.
Last edited by amberk1263; 04-11-2014 at 08:12 AM.
Is this a training / testing exercise that you are supposed to be doing?
What have you tried so far?
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Yes Olly it is training/testing exercise. I was also working on it manually, like first counted all characters by LEN(A1) formula and then applied Proper Formula. but want to know the exact formula by which i can capitalize texts with more than 100 characters only.
Try this one
=PROPER(A1)
and copy down
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
=Proper(A1) is only for converting it to proper, but what if i have texts containing characters less than and more than 100 and i want to apply formula to only those which are greater than 100.
Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.
To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Share the formula you have come up with so far, then we can advise where you need to change it
Olly please check formulas here, first i used LEN formula to count characters and then applied the formula mentioned in the screenshot.
http://i.imgur.com/4JwWvSp.png?1
hi amberk1263 ,
Give example of name. you can try to use the text formula ="upper", "if", "len" and "mid"
if(len("text">100,Upper(mid("text",1,1)
Hi blue,
Examples of Names are given in this screenshot.
http://i.imgur.com/P2ndxPz.png?1
Great, you're just about there. All you need to do is combine the two formulas:
Formula:
=IF(LEN(A2)>100,PROPER(A2),"")
This returns a blank cell if the length of A2 is 100 or less. Change the final "" argument if you want to change that.
Great, Thanks Olly Solved ,
Your title still does not confirm to the rules.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks