We talked about RIGHT, LEFT, MID, UPPER, LOWER, PROPER, FIND, SEARCH, REPLACE and SUBSTITUTE in the past few weeks. Now it’s time to put every thing together to solve a problem. This is actually the most amazing part of using Excel functions: A single function may not solve your problem. A combination of functions could do a job that is out of your imagination. :)
Here’s the task:
What we need to do is to convert the poorly written texts on A2:A10 into what we need, illustrated on B2:B10.
- Everything to the left of the hyphen “-” should be in upper cases
- Everything to the right of the hyphen “-” should be in proper cases
- Extra spaces should be removed. (tip: TRIM can do it easily)
TRIM(text) – Removes all spaces from text except for single space between words
Please consider this an exercise to see how well you know the text-related functions in Excel. You may use helper columns, or a single formula as long as your formula lead to the final results expected. Please leave your answer in comments.
Looking forward to your contributions !🙂
You may download a sample file here:Excel Exercise – Text Manipulation