#1
|
||||
|
||||
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. |
#2
|
||||
|
||||
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:
=SUBSTITUTE(PROPER(SUBSTITUTE(TEXTJOIN(",",TRUE,E3 :G3),"-","qzqzqz")),"qzqzqz","-") |
#3
|
||||
|
||||
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. |
#4
|
||||
|
||||
[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". |
#5
|
||||
|
||||
It would be a lot cleaner to do this in Power Query, since you're already using it.
|
#6
|
||||
|
||||
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.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Extract all characters from the nth number of a string | Marcia | Excel | 2 | 02-24-2020 05:15 PM |
Wildcard replace any string in context with a specified string | wardw | Word | 7 | 05-07-2018 09:13 AM |
Replace characters in a string | Anthon | Excel Programming | 1 | 11-03-2016 12:48 AM |
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 |
Retrieve characters after nth occurence of a string | veedee | Excel | 5 | 06-16-2014 03:41 PM |