Thread: [Solved] Replace characters in a string
View Single Post
 
Old 04-30-2020, 04:29 AM
p45cal's Avatar
p45cal p45cal is offline Windows 10 Office 2019
Expert
  
Join Date: Apr 2014
Posts: 871
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
You don't say where you're applying the PROPER function but I'm gessing on the sheet (because there is no PROPER function (I don't think) in Power Query), but you can do this in Power Query with the Capitalize Each Word transformation. The only problem is that you end up with the same problem, but it's easier to get round in Power Query:
  1. Replace hyphens with some sequence of letters you'll never meet in a name, eg:qzqzqz
  2. Capitalize Each Word
  3. Replace qzqzqz with a hyphen
The worksheet formula equivalent might be along the lines of:
=SUBSTITUTE(PROPER(SUBSTITUTE(TEXTJOIN(",",TRUE,E3 :G3),"-","qzqzqz")),"qzqzqz","-")
Reply With Quote