## Text manipulation with Excel functions

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.  🙂

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 ! 🙂

This entry was posted in Formula and tagged , , , , , , , , , , . Bookmark the permalink.

### 6 Responses to Text manipulation with Excel functions

1. Dirk says:

Here is another approach:
=TRIM(SUBSTITUTE(SUBSTITUTE(PROPER(A2),”Chapter”,”CHAPTER”),”-“,” – “))

Like

• Dirk says:

or as array formula to avoid using substitute twice:
{=TRIM(SUBSTITUTE(PROPER(A2),{“Chapter”,”-“},{“CHAPTER”,” – “}))}

Like

• MF says:

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.

Like

2. Hossat says:

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

Like

3. Michael (Micky) Avidan says:

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

Like

• MF says:

Hi Michael,
Thanks so much for your suggestion. I’ve attached the workbook now.
Cheers,

Like