How to capitalize everything before a certain character?

[Burrgogi]

In column A, I have some text:
Hello good day.mp3
Hello good day.flac
etc.

I'd like to capitalize everything before the period. I don't need the file extension capitalized.

I have a feeling that I need to combine the PROPER function with the FIND function but I'm struggling to find the right formula. I'm lost.

 

[Toadstool]

Hi Burrgogi,

In many cases an apostrophe will be followed by an "s" so an extra SUBSTITUTE will fix that.

Burrgogi.xlsx
	A	B
1	Hello good day.mp3	Hello Good Day.mp3
2	Hello good day.flac	Hello Good Day.flac
3	He's Funny That Way.mp3	He's Funny That Way.mp3
4	III Some Filename.mp3	Iii Some Filename.mp3
5	XI Kyrie Eleison.mp3	Xi Kyrie Eleison.mp3
Sheet1 (3)

Cell Formulas
Range	Formula
B1:B5	B1	=SUBSTITUTE(PROPER(LEFT(A1,FIND(".",A1)))&RIGHT(A1,LEN(A1)-FIND(".",A1)),"'S","'s")

 

[mse330]

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 ?

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]

Challenges, always challenges

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).

 

[mse330]

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]

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]

@Rick,

Your function results is marked in red

III Some Filename.mp3	Iii Some Filename.Mp3
XI Kyrie Eleison.mp3	Xi Kyrie Eleison.Mp3

please can you modify to results:

III Some Filename.Mp3
XI Kyrie Eleison.Mp3

 

[Burrgogi]

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]

#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.

 

[mse330]

@Burrgogi, have you tried the function in post #12 ? Does it give any wrong output ? If yes, please show us an example

 

[Peter_SSs]

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.

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
	A	B	C	D
1	Data	Post #12	Post #13	This Post
2	Hello good day.mp3	Hello Good Day.mp3	Hello Good Day.Mp3	Hello Good Day.mp3
3	Hello good day.flac	Hello Good Day.flac	Hello Good Day.Flac	Hello Good Day.flac
4	He's Funny That Way.mp3	He's Funny That Way.mp3	He's Funny That Way.Mp3	He's Funny That Way.mp3
5	III Some Filename.mp3	III Some Filename.mp3	Iii Some Filename.Mp3	III Some Filename.mp3
6	XI Kyrie Eleison.mp3	XI Kyrie Eleison.mp3	Xi Kyrie Eleison.Mp3	XI Kyrie Eleison.mp3
7	john 'big man' jones.mp3	John 'big Man' Jones.mp3	John 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
10	road(s) to rome.abc	Road(s) To Rome.abc	Road(S) To Rome.Abc	Road(s) To Rome.abc
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=CapIt(A2)