How to capitalize everything before a certain character?

Burrgogi

Board Regular
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.

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Rick Rothstein

MrExcel MVP
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

Well-known Member
Don't have Excel but try
Rich (BB code):
``=UPPER(LEFT(A1,FIND(".",A1,1)))&MID(A1,FIND(".",A1)+1,10)``

Burrgogi

Board Regular
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

Well-known Member
Hi Burrgogi,

Is this what you wanted?

Book1
AB
1Hello good day.mp3
HELLO GOOD DAY.mp3
2Hello good day.flac
HELLO GOOD DAY.flac
Sheet1
Cell Formulas
RangeFormula
B1:B2B1=UPPER(LEFT(A1,FIND(".",A1)))&RIGHT(A1,LEN(A1)-FIND(".",A1))

Well-known Member
Ah! I just saw your clarification.

Book1
AB
1Hello good day.mp3
Hello Good Day.mp3
2Hello good day.flac
Hello Good Day.flac
Sheet1 (2)
Cell Formulas
RangeFormula
B1:B2B1=PROPER(LEFT(A1,FIND(".",A1)))&RIGHT(A1,LEN(A1)-FIND(".",A1))

Michael M

Well-known Member
Ok, change to
Rich (BB code):
``=Proper(LEFT(A1,FIND(".",A1,1)))&MID(A1,FIND(".",A1)+1,10)``

Burrgogi

Board Regular
Ah! I just saw your clarification.

Book1
AB
1Hello good day.mp3
Hello Good Day.mp3
2Hello good day.flac
Hello Good Day.flac
Sheet1 (2)
Cell Formulas
RangeFormula
B1:B2B1=PROPER(LEFT(A1,FIND(".",A1)))&RIGHT(A1,LEN(A1)-FIND(".",A1))
Worked like a charm. I appreciate the help Toadstool.

Rick Rothstein

MrExcel MVP
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

Board Regular
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?