Text formatting in a string

[mandarp22]

Hi All

I am trying to format text in a string. I receive this worksheet from third party and I have to print visiting cards using this information. Data received contains lot of formatting errors like upper and lower case, double space between words, which I cleared the most of it with the help of formulas but I am stuck at one point with the field of designation.

In this field I need to make it in proper case but if the string contains a dash - (sometimes en dash - CHAR(150)) and if the length of the first word after dash is <=3 then that whole word should be in upper case and after that word rest of the words should be in proper case.

Also the en dash needs to be converted to normal dash. Can it be done using formulas?

I am attaching a sample sheet

Please guide

Regards

Mandar

 

[Chihiro]

Yes, it can be done. Though I'd recommend other tools if available (PowerQuery, VBA etc).

For ease of following logic, I've used helper columns to show steps.

See attached.

 

[bosco_yip]

Or, try…………………………..

1] Select E4 >> Define Name >>
Name : ReplaceDash
Refers to : =PROPER(SUBSTITUTE(TRIM(Sheet2!$B4),CHAR(150),"-"))

Then,

2] In E4, enter formula and copied down :

=IF(MID(ReplaceDash&" ",FIND("-",ReplaceDash&"-")+5,1)=" ",REPLACE(ReplaceDash,FIND("-",ReplaceDash&"-")+2,3,UPPER(MID(ReplaceDash&" ",FIND("-",ReplaceDash&"-")+2,3))),ReplaceDash)

Regards
Bosco