How to capitalize everything before a certain character?

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
627
Office Version
2016
Platform
Windows

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
595
Office Version
365
Platform
Windows
This is another option using a UDF which should work considering there's always a space between the words & should work correctly in something like "I'd love you.mp3" without capitalizing the d in I'd … I'm sure Rick will come up with something more robust & crazy one-liner UDF 😉

VBA Code:
Function Cap(Txt As String) As String
   For x = 0 To UBound(Split(Txt))
      Cap = Cap & j & UCase(Left(Split(Txt)(x), 1)) & Mid(Split(Txt)(x), 2)
      j = " "
   Next
End Function
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
Challenges, always challenges :giggle:
VBA Code:
Function Cap(Txt As String) As String
  Cap = Evaluate("SUBSTITUTE(PROPER(SUBSTITUTE(""" & Txt & """,""'"",""zxzx"")),""zxzx"",""'"")")
End Function
Note 1: This will work for any letter or letters following the apostrophe (e.g., it's, I'm, we'd, you'll, etc.)

Note 2: This will not work correctly if you have text encased in apostrophes (e.g., john 'big man' jones).
 
Last edited:

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
595
Office Version
365
Platform
Windows
While the proper function won't be suitable for the OP requirement due to examples like "XI Kyrie Eleison.mp3" but I'm really amazed of how you always come up with alternative solutions such as the apostrophes smartly with the "zxzx" … Always learning from you Rick & thanks for the knowledge sharing :)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
Always learning from you Rick & thanks for the knowledge sharing
I am always happy to share whatever it is that I have "learned" in some 39 years of programming with whoever finds it useful.
 

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
256
@Rick,

Your function results is marked in red
III Some Filename.mp3Iii Some Filename.Mp3
XI Kyrie Eleison.mp3Xi Kyrie Eleison.Mp3
please can you modify to results:

III Some Filename.Mp3
XI Kyrie Eleison.Mp3
 

Burrgogi

Board Regular
Joined
Nov 3, 2005
Messages
236
Hi Burrgogi,

In many cases an apostrophe will be followed by an "s" so an extra SUBSTITUTE will fix that.
If you haven't noticed by now, these are song/music titles I'm working with so that's not a safe assumption to make.
There is a variety of letters besides the 's'. For example:

I'm
I've
Shouldn't
etc.

These are just a few examples - there are also french songs involved here. Example:
C'etait Ce Soir.mp3
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
#2. The PROPER function is kind of mucking up the works a bit. It's converting second letter (actually all letters after the 1st one) in these particular scenarios lowercase which is definitely not what I want.
III Some Filename.mp3
XI Kyrie Eleison.mp3

Becomes this:
Iii Some Filename.mp3
Xi Kyrie Eleison.mp3
We are going to need a "rule" from you on how to proceed. I can understand the problem with III, but Xi is a real word (14th letter of the Greek alphabet) so how is the code supposed to distinguish it as not a word in your usage. Is the "rule" to be - if the word contains all upper case letters, leave the word as is? Or, since we do not know what your data actually looks like (maybe always all lower case letters), maybe the "rule" should be if there is any upper case letter anywhere within the word, then leave the word as is? You need to tell us the "rule" that the code should follow.
 
Last edited:

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
595
Office Version
365
Platform
Windows
@Burrgogi, have you tried the function in post #12 ? Does it give any wrong output ? If yes, please show us an example
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,458
Office Version
365
Platform
Windows
This sort of thing is notoriously tricky, but here is another udf attempt and some comparisons with earlier udfs.
I note that Rick's udf capitalises the file extension as well. The OP said "I don't need the file extension capitalized" but not "I don't want the file extension capitalized" so I'm not sure if that is an issue and have just highlighted other examples where I think there is or could be a problem with the previous udfs. Most likely examples where mine fails will surface too. :)

VBA Code:
Function CapIt(Txt As String) As String
  Dim RX As Object, itm As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(^| )([^a-zA-Z]*)([a-z])"
  For Each itm In RX.Execute(Txt)
    Mid(Txt, itm.firstindex + 1, itm.Length) = UCase(Mid(Txt, itm.firstindex + 1, itm.Length))
  Next itm
  CapIt = Txt
End Function
Burrgogi 2020-05-18 1.xlsm
ABCD
1DataPost #12Post #13This Post
2Hello good day.mp3Hello Good Day.mp3Hello Good Day.Mp3
Hello Good Day.mp3
3Hello good day.flacHello Good Day.flacHello Good Day.Flac
Hello Good Day.flac
4He's Funny That Way.mp3He's Funny That Way.mp3He's Funny That Way.Mp3
He's Funny That Way.mp3
5III Some Filename.mp3III Some Filename.mp3Iii Some Filename.Mp3
III Some Filename.mp3
6XI Kyrie Eleison.mp3XI Kyrie Eleison.mp3Xi Kyrie Eleison.Mp3
XI Kyrie Eleison.mp3
7john 'big man' jones.mp3John 'big Man' Jones.mp3John Zxzxbig Man' Jones.Mp3
John 'Big Man' Jones.mp3
8(wanna get to know you) that good!.xxx(wanna Get To Know You) That Good!.xxx(Wanna Get To Know You) That Good!.Xxx
(Wanna Get To Know You) That Good!.xxx
9#selfie.mp3#selfie.mp3#Selfie.Mp3
#Selfie.mp3
10road(s) to rome.abcRoad(s) To Rome.abcRoad(S) To Rome.Abc
Road(s) To Rome.abc
Sheet1
Cell Formulas
RangeFormula
D2:D10D2=CapIt(A2)
 

Watch MrExcel Video

Forum statistics

Threads
1,096,187
Messages
5,448,862
Members
405,535
Latest member
KLFT

This Week's Hot Topics

Top