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.

 

[Rick Rothstein]

Can you clarify something for me (us)? When you say "capitalize everything before the period", do you mean all letters or just the first letter of each word? I ask because you mentioned the PROPER function which does the latter.

 

[Michael M]

Don't have Excel but try

=UPPER(LEFT(A1,FIND(".",A1,1)))&MID(A1,FIND(".",A1)+1,10)

 

[Burrgogi]

Can you clarify something for me (us)? When you say "capitalize everything before the period", do you mean all letters or just the first letter of each word? I ask because you mentioned the PROPER function which does the latter.

I must admit I worded that rather poorly. I should have said capitalize each word It's been a very long day for me, full of yard work.

The end result I'm trying to achieve is this:

Hello Good Day.mp3
Hello Good Day.flac

 

[Toadstool]

Hi Burrgogi,

Is this what you wanted?

Book1
	A	B
1	Hello good day.mp3	HELLO GOOD DAY.mp3
2	Hello good day.flac	HELLO GOOD DAY.flac
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=UPPER(LEFT(A1,FIND(".",A1)))&RIGHT(A1,LEN(A1)-FIND(".",A1))

 

[Toadstool]

Ah! I just saw your clarification.

Book1
	A	B
1	Hello good day.mp3	Hello Good Day.mp3
2	Hello good day.flac	Hello Good Day.flac
Sheet1 (2)

Cell Formulas
Range	Formula
B1:B2	B1	=PROPER(LEFT(A1,FIND(".",A1)))&RIGHT(A1,LEN(A1)-FIND(".",A1))

 

[Michael M]

Ok, change to

=Proper(LEFT(A1,FIND(".",A1,1)))&MID(A1,FIND(".",A1)+1,10)

 

[Burrgogi]

Ah! I just saw your clarification.

Book1
	A	B
1	Hello good day.mp3	Hello Good Day.mp3
2	Hello good day.flac	Hello Good Day.flac
Sheet1 (2)

Cell Formulas
Range	Formula
B1:B2	B1	=PROPER(LEFT(A1,FIND(".",A1)))&RIGHT(A1,LEN(A1)-FIND(".",A1))

Worked like a charm. I appreciate the help Toadstool.

 

[Rick Rothstein]

Another question (your answer might affect the function of the formulas you have received)... is it possible for there to be period (say, on an abbreviation perhaps) appearing before the period in front of the file extension?

 

[Burrgogi]

Another question (your answer might affect the function of the formulas you have received)... is it possible for there to be period (say, on an abbreviation perhaps) appearing before the period in front of the file extension?

Hi Rick, there are no other periods that appear in the filename. I have double checked.

Since trying out Toadstool's formula, I have discovered 2 unexpected problems:

#1.
I have apostrophies appearing in some of the filenames. For example, using his formula makes turns this filename:
He's Funny That Way.mp3

Into this:
He'S Funny That Way.mp3


#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

Any thoughts?