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...
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]
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.
Share the formula you have come up with so far, then we can advise where you need to change it
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)
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
Great, you're just about there. All you need to do is combine the two formulas:
Formula:
Please Login or Register to view this content.
This returns a blank cell if the length of A2 is 100 or less. Change the final "" argument if you want to change that.
Hi blue,
Examples of Names are given in this screenshot.
http://i.imgur.com/P2ndxPz.png?1
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