• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing workbook full of "shouty capitals" into proper case

Safequip

Safequip

Member
I know there is a formula for doing this, but for the life of me I can't remember what it its, any help is as always appreciated.

Thansk
 
lower() upper() proper()
THE the THE The
QUICK quick QUICK Quick
BROWN brown BROWN Brown
FOX fox FOX Fox
JUMPED jumped JUMPED Jumped
 
Sorry, that didn't come out right:

lower - returns all lower case
upper - returns all upper case
proper - returns it in what excel thinks will be proper case.
 
Thanks for this, however I can't seem to get it to work I need to have all of column b changed (there are about 200 cells)
 
see attached I need this all to be prober case.. ie Safequip Ltd rather than SAFEQUIP LTD, hope this makes sense.
 

Attachments

  • help.xlsx
    13.7 KB · Views: 5
Hi,

I would say that probably some in your spreadsheet should be all capitals i.e. BDA DESIGN probably BDA Design, only you can answer that one?

But have you tried using the =PROPER() function as dan_l suggested?
 
There is also a quick way in correcting things, by using the Find/Replace menu, for instance I notice that after using the Proper function (UK) got changed to (Uk) so see attached.
BTW where UK is without brackets that also has been changed to Uk, but when you use Find/Replace for that one included a space before and after UK in both cases just in case.
Proper.jpg
 
Hi, Safequip!
I agree with oldchippy, you don't have a straight way to do it with formulas as you can't foresee all the conditions to change and to exclude from changing, so depending on the no. of records of your actual file I'll suggest you 2 methods:
a) If the number is little, use PROPER formula, then copy & paste values over the original ones; later adjust them manually as required.
b) If the number isn't litte, use UPPER formula.
Regards!
 
Hi, Safequip!
Just curious, how did you get it? It might be useful for people with the same issue.
Regards!
 
Hi Sir I did as Dan said, but where I was going wrong was I wasn't using Colum "B" to type in the formula so to change all of colum A I typed = Proper (A1) into B1 and dragged to the bottom .. easy when you have the great advise of the members here and it makes me look clever to the boss
 
Hi, Safeqúio!
Thanks for sharing your solution with the community, so as people who read this could have the issue or question and the solution or answer as well.
Regards!
 
You must log in or register to reply here.
Back
Top