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
wmfexcel 
Here is another approach:
=TRIM(SUBSTITUTE(SUBSTITUTE(PROPER(A2),”Chapter”,”CHAPTER”),”-“,” – “))
LikeLike
or as array formula to avoid using substitute twice:
{=TRIM(SUBSTITUTE(PROPER(A2),{“Chapter”,”-“},{“CHAPTER”,” – “}))}
LikeLike
Hi Dirk, I like your approach. It works for our example very well, given all starts with “chapter” assuming there is no typo. 🙂
However I think array formula doesn’t work the way you expect for SUBSTITUTE.
To use one next SUBSTITUTE, how about
=”CHAPTER ” & TRIM(SUBSTITUTE(PROPER(MID(A2,8,LEN(A2))),”-“,” – “))
Of course, it assumes all the cells start with a word with 7 characters.
LikeLike
Hi, thank you for this kind of exercice, hereafter a simple solution:
=UPPER((TRIM(LEFT(A2;FIND(“-“;A2)-1))))&” – “&PROPER(TRIM(MID(A2;FIND(“-“;A2)+1;99)))
Hope there will be another “homeworks” 😉
Hocine – Alg DZ
LikeLike
One of several solutions can be:
=TRIM(UPPER(LEFT(A2,FIND(“-“,A2)-1))&” – “&PROPER(MID(A2,FIND(“-“,A2)+1,255)))
PS It would be nice if you’ll conside to attach you workbook to tasks to make it easier for us.
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL
LikeLike
Hi Michael,
Thanks so much for your suggestion. I’ve attached the workbook now.
Cheers,
LikeLike