Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-29-2020, 05:08 PM
Marcia's Avatar
Marcia Marcia is offline Replace characters in a string Windows 7 32bit Replace characters in a string Office 2013
Expert
Replace characters in a string
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Replace characters in a string


Hi. A workbook has several sheets. Columns E,F and G of 14 sheets contain LastName, FirstName and MiddleName all in upper cases. In col T (FullName) I joined cols E,F,G but in proper case. I merge all 14 sheets by power query, re arranging the position of the FullName, it is no longer in column T.
My problem is that the letters right after the hyphens are capitalized by the PROPER function in col T which is wrong. (i.e from AW-AN it was changed to Aw-An when it should be Aw-an). Everytime I refresh the merge, I perform the find all the capital letters with a hyphen before them, then replace with the small letter which is time consuming. My search led me to this vba but when I ran it, nothing changed.
Sub VBA_Replace()
Str1 = "-A"
Str1 = Replace (str1, "-A", "-a")
Str2 = "-E"
Str2 = Replace (str2, "-E", "-e")
Str3 = "-I"
Str3 = Replace (str3, "-I", "-i")
..... until O and U
End Sub
I would like the above code to find all capital letters after a hypen in column FullName then replace them to small letters.
Kindly help, thank you.
Also, there are name extensions like II and III but only 3 cases so I just manually change them from Ii and Iii to II and III. BTW they are not descendants of royalty and the pope.
Internet signal here is so weak, I couldn't use the laptop to attach a sample.
Reply With Quote
  #2  
Old 04-30-2020, 04:29 AM
p45cal's Avatar
p45cal p45cal is online now Replace characters in a string Windows 10 Replace characters in a string Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
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
  #3  
Old 04-30-2020, 06:20 PM
Marcia's Avatar
Marcia Marcia is offline Replace characters in a string Windows 7 32bit Replace characters in a string Office 2013
Expert
Replace characters in a string
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

I think the SUBSTITUTE formula will make me replace "qz" to hyphen instead of the several A E I O U. I will try this approach.
I'm so new to power query, I didn't know about the Capitalize Each Word option.
Thank you.
Reply With Quote
  #4  
Old 05-04-2020, 03:51 AM
Marcia's Avatar
Marcia Marcia is offline Replace characters in a string Windows 7 32bit Replace characters in a string Office 2013
Expert
Replace characters in a string
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

[QUOTE=
The worksheet formula equivalent might be along the lines of:
=SUBSTITUTE(PROPER(SUBSTITUTE(TEXTJOIN(",",TRUE,E3 :G3),"-","qzqzqz")),"qzqzqz","-")[/QUOTE]

I applied this formula and it is perfect, though I limited the "qzqzqz" to just "qz".
Reply With Quote
  #5  
Old 05-04-2020, 03:59 AM
p45cal's Avatar
p45cal p45cal is online now Replace characters in a string Windows 10 Replace characters in a string Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by Marcia View Post
Everytime I refresh the merge
It would be a lot cleaner to do this in Power Query, since you're already using it.
Reply With Quote
  #6  
Old 05-04-2020, 05:15 AM
Marcia's Avatar
Marcia Marcia is offline Replace characters in a string Windows 7 32bit Replace characters in a string Office 2013
Expert
Replace characters in a string
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by p45cal View Post
It would be a lot cleaner to do this in Power Query, since you're already using it.
Capitalize Each Word in power query was mentioned earlier but I was squeezed of precious minutes to find it out. Now that I had time to explore it, I found out that I could do away with the several helper columns in the data source. Again, thank you.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Replace characters in a string Extract all characters from the nth number of a string Marcia Excel 2 02-24-2020 05:15 PM
Replace characters in a string Wildcard replace any string in context with a specified string wardw Word 7 05-07-2018 09:13 AM
Replace characters in a string Replace characters in a string Anthon Excel Programming 1 11-03-2016 12:48 AM
Replace characters in a string find a set of characters in a string and return a 0 (zero) if not found MaineLady Excel 2 11-05-2015 03:23 PM
Replace characters in a string Retrieve characters after nth occurence of a string veedee Excel 5 06-16-2014 03:41 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:12 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft