Hi, I need a formula to convert a text in all upper cases into lower cases except for the first letter in the first word after each equal sign. However, the text before the equal sign must still be in capital letters. I currentrly use the function PROPER which did not give me the desired result. Please see attachment.
I could offer you a formula that would deal with ONE = sign and make everything CAPS before it and Proper after it. For delimited strings like you are demonstrating I would resort to VBA programming to resolve this scenario.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Without VBA, this would only be possible if you had a fixed number of entries in each cell in Column A (i.e. you had the same number of "=" signs in every cell). Without this knowledge, there is no formula that I am aware of that will accomplish what it is that you are trying to achieve unfortunately.
To achieve the result in VBA, place a button onto your worksheet, and place the following code into your button's Click event (my button is called "CommandButton1"):
Please Login or Register to view this content.
Yes, As JBeaucaire says VBA would be the solution.
But if you would like to have a non VBA solution, see if the attached kind of work around helps.
This is above formula used for the first BUS=BUSINESS, RES=RESIDENTIAL
Please Login or Register to view this content.
If you do want a VBA function, then maybe this:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Try this UDF which will work with N number of permutation combination.
Please Login or Register to view this content.Formula:
Please Login or Register to view this content.
Or
Formula:Please Login or Register to view this content.
How to install your new code
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Choose Insert > Module
- Edit > Paste the macro into the module that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
That code was very impressing Sixthsense!!!
Thanks for the feedback and rep
But the code of TMShucks is having lot more functionalities/features than my suggested code
Thank you all for your suggestions. For now, I will use the Excel formula since I'm on tight deadline and VBA for the next project.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Just for fun this formulae approach can be extended to suit any length of string.
Name:="Split_Str"
Refers to:=
Formula:Please Login or Register to view this content.
Then in C2, Drag across as far as required to split your longest string, then Down
Formula:Please Login or Register to view this content.
Concatenate the results in B2
=Formula:Please Login or Register to view this content.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
You're welcome. Thanks for the rep.
Seems a shame not to use the code solution when all you have to do is copy and paste the code into a standard module. It will work "out of the box".
But, your choice. Good luck with your project.
Regards, TMS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks